<% 'open connection 'myDSN="DSN=focus" 'set conntemp=server.createobject("adodb.connection") 'conntemp.open myDSN 'my connection and vars dim conn, strMDBPath, strsql, strsql1, rs, rs1, sqlStr, sqlStr2, sqlStr3 set conn = Server.CreateObject("ADODB.Connection") set rs = Server.CreateObject("ADODB.Recordset") set rs1 = Server.CreateObject("ADODB.Recordset") strMDBPath = "e:\domains\s\spconnor.co.uk\user\private\focus.mdb" conn.open "PRoVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath 'start with an sql ' now I bring all the entries back fromthe db sqlStr = "TRANSFORM Count(PEWDB.PewNumber) AS [The Value] SELECT 'Archived' AS Status FROM PEWDB WHERE (((PEWDB.archive)='-1')) GROUP BY 'Archived', PEWDB.archive PIVOT Format([startdate],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');" sqlStr2 = "TRANSFORM Count(PEWDB.PewNumber) AS [The Value] SELECT 'Live' AS Status FROM PEWDB WHERE (((PEWDB.archive)='0')) GROUP BY 'Live', PEWDB.archive PIVOT Format([startdate],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');" sqlStr3 = "TRANSFORM Count(PEWDB.PewNumber) AS [The Value]SELECT 'Total' AS Status FROM PEWDB GROUP BY 'Total' PIVOT Format([startdate],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');" 'sqlStr = "TRANSFORM Count(PEWDB.PewNumber) AS [The Value]SELECT Mid([startdate],4,2) AS startdate1 FROM PEWDB GROUP BY Mid([startdate],4,2) PIVOT Format([startdate],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');" 'sqlStr = "SELECT PEWDB.archive, Count(PEWDB.startdate) AS [Pew Count], Mid([startdate],4,2) AS [month] FROM PEWDB GROUP BY PEWDB.archive, Mid([startdate],4,2);" 'response.Write ("
" & sqlStr) set rs = conn.execute (sqlStr) set rs2 = conn.execute (sqlStr2) set rs3 = conn.execute (sqlStr3) howmanyfields=rs.fields.count -1 %> istc pew list
 

Enter New PEW     Authorised Pews    Archive Pews    

 

These measures are based on the start dates that have been entered into the system.

 

<%for i =0 to howmanyfields %> <%next%> <% Do While Not rs.EOF %> <%for i =1 to howmanyfields%> <%next rs.movenext %> <% Loop %> <% Do While Not rs2.EOF %> <%for i =1 to howmanyfields%> <%next rs2.movenext %> <% Loop %> <% Do While Not rs3.EOF %> <%for i =1 to howmanyfields%> <%next rs3.movenext %> <% Loop %>
<% =rs(i).name %>
<%=rs(0)%>  <%=rs(i)%> 
<%=rs2(0)%>  <%=rs2(i)%> 
<%=rs3(0)%>  <%=rs3(i)%> 

 

 

 

 

<% ' Clean Up set rs=nothing conn.Close Set conn= Nothing %>

<< Back