Change order of COLUMNS in CROSSTAB

Giganews Newsgroups
Subject: Change order of COLUMNS in CROSSTAB
Posted by:  mmcqua…@gmail.com
Date: 2 Aug 2006

Hey gang,

I just created a crosstab query to assist in generating reports for
class attendance.

When I run the report (using temp hard-coded date constraints), I
notice that my column headings are in what seems to be a random order.
What I'd like to do (at least on the reporting end), is show the dates
from LEFT to RIGHT in ASCENDING order.

Below is my SQL and some code I found (thanks to DSF) on this forum
that i'll be incorporating into my report.  Can this date-ordering
thing be taken care of in the SQL, or is it a runtime thing when
generating the report?

Thanks in advance!

Here is my SQL for the cross-tab:
-------------------------------------------------------
TRANSFORM Sum(URKIDS_ATTENDANCE.ABSENT) AS SumOfABSENT
SELECT URKIDS_ATTENDANCE.URKIDSID, URKIDS_ATTENDANCE.GROUPID,
Sum(URKIDS_ATTENDANCE.ABSENT) AS [Total Of ABSENT]
FROM URKIDS_GROUP RIGHT JOIN URKIDS_ATTENDANCE ON URKIDS_GROUP.ID =
URKIDS_ATTENDANCE.GROUPID
WHERE (((URKIDS_ATTENDANCE.CLASS_DATE)>=#8/1/2004# And
(URKIDS_ATTENDANCE.CLASS_DATE)<=#8/2/2007#))
GROUP BY URKIDS_ATTENDANCE.URKIDSID, URKIDS_ATTENDANCE.GROUPID,
URKIDS_GROUP.SEASON
ORDER BY URKIDS_GROUP.SEASON
PIVOT Format([CLASS_DATE],"Short Date");

Here is some code I found that I will eventually incorportate into my
report:
-----------------------------------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)

Set db = CurrentDb()

'LINE UP CONTROLS
For i = 1 To 10
  Me("txt" & i).Left = Me("lbl" & i).Left
  Me("txt" & i).Width = Me("lbl" & i).Width
  Me("sumTxt" & i).Left = Me("lbl" & i).Left
  Me("sumTxt" & i).Width = Me("lbl" & i).Width
Next i

'SINCE THE CROSS-TAB RESULTS AREN'T KNOWN UNTIL THE QUERY IS RUN
'BIND THE CONTROLS AND SET LABELS AT RUN TIME

'FIRST BLANK OUT ALL LABELS AND TOTALS
For i = 1 To 10
  Me("lbl" & i).Caption = ""
  Me("sumTxt" & i).Visible = False
Next i

'BIND CONTROLS
j = 1
Set rs = db.OpenRecordset("CROSSTAB_QUERY")
  If j <= 10 Then  'MAX OF 10 TEXT BOXES
  For i = 3 To rs.Fields.Count - 1  '3 IS THE FIRST DATA FIELD
    Me("lbl" & (j)).Caption = rs(i).Name
    Me("txt" & (j)).ControlSource = rs(i).Name
    Me("sumTxt" & j).Visible = True
    j = j + 1
    Next i
  End If
rs.Close

End Sub

Replies