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

PW wrote:[color=blue]
> 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 ...
>[/color]
<snip of ugly concatenation>[color=blue]
> When I try to output the results, I do it like this ...
>[/color]
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"

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%231FMQ7zeGHA.4912@TK2MSFTNGP05.phx.gbl...[color=blue]
> PW wrote:[color=green]
>> 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 ...
>>[/color]
> <snip of ugly concatenation>[color=green]
>> When I try to output the results, I do it like this ...
>>[/color]
> 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"
>[/color]


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