473,289 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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


May 19 '06 #1
0 1112

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Randy Harris | last post by:
I've been trying to construct a Crosstab query (mess pasted below) that would output all rows, even if the Value (a count) is 0. No matter what I do, it only returns rows that have matching...
3
by: John | last post by:
I've read several prior posts in this group about using nz() to convert null values to zero; however, I'm not sure how/where to implement this function in my crosstab query. The crosstab query...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
4
by: No Spam | last post by:
Dear Access 2000 users, I have a crosstab query that puts together certain information perfectly. It has a criteria that is based on a form that limits how many columns are returned based on...
2
by: scott.k.fraley | last post by:
....and the SELECT thats trying to pull from said Query doesn't like it one bit! ;) I'm working on this project (in Access 2002) and there is a report who's RecordSource is the following...
2
by: jason.teen | last post by:
Hi All, I am having trouble creating a crosstab query. In my original data I have two columns, One called "Categorized" and one called "Mapped' in which those columns can hold values of "true"...
2
by: Mike | last post by:
Hi everyone, I found the Access reports too limited to do what i wanted so i created a module to export a crosstab query to an excel file, and then i modify it as i want. My problem is that i...
2
by: Phil Latio | last post by:
My quandary is this..... I have a label application (Bartender) that I have hooked up to Access, to use the output of a cross tab. I've succeeded in getting the data within Access, but my...
1
by: bobykim | last post by:
Hi All, I'm using MS Access 2003 in a Windows XP environment. I've created an aging report for my department that is based on what I call the "Main query" with an IIf statement that allows the...
23
by: helm | last post by:
Folks, could anyone advise ... Is there a significant difference in crosstab capabilities in Access and Excel? Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.