Looping through crosstab query recordset

[BananaQuaalude]

I am using the following cross-tab query to form a recordset:

strSQL = "TRANSFORM Max(tblCrossTab.Hours) AS MaxOfHours SELECT tblCrossTab.EmpName, Sum(tblCrossTab.Hours) AS [Total Of Hours] FROM tblCrossTab GROUP BY tblCrossTab.EmpName PIVOT tblCrossTab.Month;"

How do I loop through the contents of that query to populate an HTML table? The following code produces the "Item cannot be found in the collection corresponding to the requested name or ordinal" error on the "MaxOfHours" line:

<% do until RS.EOF %>
<tr>
<td><%=RS("EmpName")%></td>
<td><%=RS("MaxOfHours")%></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<% RS.MoveNext
loop

I've tried referencing the other fields in the query, but the only field I can get out of the recordset is the EmpName. Do I need to change the query, or is the data there but I'm not referencing it correctly?

Thanks!

[khaki]

hi BananaQuaalude...

MaxOfHours is not a record.
try this:

<% do until RS.EOF %>
<tr>
<td><%=RS("EmpName")%></td>
<td><%response.write MaxOfHours %></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<% RS.MoveNext
loop

;) k

EDIT:
okay... wait....
(i see...)

[BananaQuaalude]

No error on that, but no data prints either. Is there a way to loop through all the elements in the recordset, without specifying the field? That would not be as convenient, but I might be able to make it work.

???

[khaki]

hi again BananaQuaalude...

ummm...
now that I look at it...
i don't think that you wrote the TRANSFORM statement correctly either (although I usually rely on such things from my DBA... and unfortunately he is on vacation at the moment)...

but anyway...

i think that you need to change the SELECT statement to include only one field....
although you may be doing some complex nested version that I've never seen before (is it even possible?)

But anyhow... I think that you are asking that statement to do too much (even for a pivot)...
although I am by no means an expert in this area.

maybe you know that it should work as it is...
but if not...
try doing it with using only EmpName (take out the Sum part) and see if it works that way.

sorry BananaQuaalude... but I only know enough about this statement to get me into trouble... not out of it :rolleyes:

so... if you want to... simplify it and tell me how it goes (and we can work on it from there).
Otherwise... I'm stumped :rolleyes:

;) k

[khaki]

okay BananaQuaalude...

I still don't think that you wrote the statement correctly...
but you can get the returned names/values of records by using this:
(some minor changes to Dave's suggestion):

<%
Do While Not RS.EOF

For Each fld in RS.Fields
Response.Write "<p>" & fld.name & " = " & fld.Value & "</p>" & vbCrLf
Next

RS.MoveNext
Loop
%>

although.... with your statement....
i just don't know.

keep us up to date (please :)... this is an interesting one ) ...
;) k

[BananaQuaalude]

Okay, so the SQL statement is okay, and the RS.Fields method did not work, although khaki- I didn't try your second method.

This thing was a nightmare, and I still have to make it editable by putting everything into textboxes, but here is what I did to get the report phase working:


strSQL_CT = "TRANSFORM Max(tblCrossTab.Hours) AS MaxOfHours SELECT tblCrossTab.EmpName, Sum(tblCrossTab.Hours) AS [Total Of Hours] FROM tblCrossTab GROUP BY tblCrossTab.EmpName PIVOT tblCrossTab.Month;"

set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "LaborDB"
set RS3 = conn.execute(strSQL_CT)

strProject = RS3.GetString
arrProject = split(strProject, chr(13))


I'm using the split() function to divide the results of RS3.GetString into rows of data that are the same as the rows of data produced by the query (chr(13) is a carriage return). It looks like this:


Christensen, Scott 240 40 40 40 40 40 40
Hinckley, Michael 540 60 60 60 60 60 60 60 60 60
McKinnie, Iain 110 10 10 10 10 10 10 10 10 10 10 10
Unassigned, Mechanical Engineer II 40 20 20
Unternahrer, Josef 100 60 40
Wood, Chris 330 30 30 30 30 30 30 30 30 30 30 30


Then, I use the split() function again to divide the data in each row up (this is so I can put each piece of data in its own <td> in the table):


<table width="100%" border="1">
<% for a = 0 to ubound(arrProject)
strData = arrProject(a)
arrData = split(strData, chr(9)) %>
<tr>
<% for i = 0 to ubound(arrData) %>
<td><%response.Write(arrData(i))%></td>

<% next %>
</tr>
<% next %>
</table>


chr(9) is a tab, which is how the data is divided in strData. Now all I have to do is add month headings to the table and I've got my report. The table looks like this:



Christensen, Scott 240 40 40 40 40 40 40
Hinckley, Michael 540 60 60 60 60 60 60 60 60 60
McKinnie, Iain 110 10 10 10 10 10 10 10 10 10 10 10
Unassigned, Mechanical Engineer II 40 20 20
Unternahrer, Josef 100 60 40
Wood, Chris 330 30 30 30 30 30 30 30 30 30 30 30



Where the spaces are represent empty months. Layed out in the HTML table, it's much more readable.

Thanks for your suggestions!! I feel like I just gave birth.

[khaki]

YAY BananaQuaalude :) !!!...

Glad you got it working....
and THANK YOU for documenting it all (it's always good to see it to the end... that way everybody learns something from it :) )

and just so ya know...
the rs.fields version that i offered definitely works (in case you ever need it... although i usually use it with tables and not with the <p> tag). Let me know if you want the code for the table structure for that method.

But anyhow...
I'm now going to have to disect the TRANSFORM statement and see if I can apply it to one my db's.
I understand the concept... it's just that my eyes rolled back in my head by the time i hit your GROUP BY part :rolleyes: LOL. Maybe I'm just overdue to have my brain de-fragged or something ;) lol.

catch you around...
;) k

[BananaQuaalude]

Hey khaki,

I'm using Access 2000 as a back-end DB for this, so I always build my queries there using the design grid and cut & paste the SQL into code. I actually have an Access 2000 form that transforms the SQL statement into neatly formatted VB code. If you would like a copy of it, I'll send it along.

Also, I did use the RS.Fields method to get the column (month) headings for the table.

Thanks very much for your help!

[khaki]

yeah... well... my head is still spinning from that pivot puzzle Dave :rolleyes:

no wonder the guy's name is Quaalude...
you need to be comfortably sedated to work with that kind of stuff :)

normally i can do this stuff in my sleep....
unfortunately i have no memory of it in the morning... :) ...
;) k