Re: Change order of COLUMNS in CROSSTAB

Giganews Newsgroups
Subject: Re: Change order of COLUMNS in CROSSTAB
Posted by:  Allen Browne (AllenBrow…@SeeSig.Invalid)
Date: Wed, 2 Aug 2006

The problem is your use of the Format() function.
The output of Format() is text.
It therefore sorts as text.

Just end the query statement with:
    PIVOT [CLASS_DATE];
and the problem should be solved.

If you did want to specify an out-of-date order, you could do that by adding
the IN operator in the PIVOT clause, e.g.:
    PIVOT [CLASS_DATE] IN (#1/1/2006#, #2/1/2006#, ...

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<mmcqua…@gmail.com> wrote in message
news:1154532369.162350.1106…@b28g2000cwb.googlegroups.com...
> 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

In response to

Change order of COLUMNS in CROSSTAB posted by mmcqua…@gmail.com on 2 Aug 2006