Crosstab query output

[PW]

I am trying to create a crosstab report in ASP. I'm using ASP/VBScript and
Access database. I used Access to create the SQL query, then copied the SQL
and embedded it in my ASP like this ...

mySQL = ""
mySQL = mySQL & "TRANSFORM Sum(Transactions.Hours) AS SumOfHours "
mySQL = mySQL & "SELECT Transactions.Activity" & myActSecond & " as
myActivity, Sum(Transactions.Hours) AS myHours "
mySQL = mySQL & "FROM Transactions "
mySQL = mySQL & "WHERE CommDate >= #" & myDateFr & "# AND CommDate <= #" &
myDateTo & "# "
mySQL = mySQL & "GROUP BY Transactions.Activity" & myActSecond & " "
mySQL = mySQL & "PIVOT Transactions.Activity" & myActFirst

When I try to output the results, I do it like this ...

rs1.open mySQL,mydsn
Do while NOT rs1.EOF
response.write rs1("myActivity")
response.write ", "
response.write rs1("myHours")
response.write "<br>"
rs1.movenext
Loop

So all I get as output is a list of the "myActivity" and values of
"myHours".

How do I get the other axis as column headings across the top?


TIA,
PW

 

[Bob Barrows [MVP]]

I am trying to create a crosstab report in ASP. I'm using
ASP/VBScript and Access database. I used Access to create the SQL
query, then copied the SQL and embedded it in my ASP like this ...

When I try to output the results, I do it like this ...

Try this:
Save your crosstab query in Access, naming it (for the sake of this example)
"TransactionsCrosstab". Then use this code in your page:

<%
dim cn, rs
set cn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation=adUseClient
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("dbname.mdb")
cn.TransactionsCrosstab rs
set rs.ActiveConnection=nothing
cn.Close:set cn=nothing
dim fld,i,val
%>
<table border="1" cellspacing="0"><tr>
<%
for each fld in rs.Fields
Response.Write "<th>" & fld.name & "</th>"
next
Response.Write "</tr>"
do until rs.EOF
Response.Write "<tr>"
for i=0 to rs.Fields.count - 1
val=rs(i).Value & ""
if len(val) = 0 then val="&nbsp;"
Response.Write "<td>" & val & "</td>"
next
Response.Write "</tr>"
rs.MoveNext
loop
rs.Close:set rs=nothing
%>
</table>

 

[PW]

Try this:
Save your crosstab query in Access, naming it (for the sake of this
example) "TransactionsCrosstab". Then use this code in your page:

<%
dim cn, rs
set cn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation=adUseClient
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("dbname.mdb")
cn.TransactionsCrosstab rs
set rs.ActiveConnection=nothing
cn.Close:set cn=nothing
dim fld,i,val
%>
<table border="1" cellspacing="0"><tr>
<%
for each fld in rs.Fields
Response.Write "<th>" & fld.name & "</th>"
next
Response.Write "</tr>"
do until rs.EOF
Response.Write "<tr>"
for i=0 to rs.Fields.count - 1
val=rs(i).Value & ""
if len(val) = 0 then val="&nbsp;"
Response.Write "<td>" & val & "</td>"
next
Response.Write "</tr>"
rs.MoveNext
loop
rs.Close:set rs=nothing
%>
</table>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks Bob. I implemented your example into my existing code and it works
great. Much obliged.