Crosstab Query Column Sort (Access97)

Topic

New Reply

I’m trying to sort by month and year for a 6 month period in a crosstab query.
Expr1: Format([LAST_SRVC_DATE],”mm/yy”) is my column heading sorted ascending.
This is producing 01/02, 02/02, 03/02, 10/01, 11/01, 12/01
Anything I try won’t sort it correctly
I want it to display as:
10/01 11/01 12/01 01/02 02/02 03/02
Help! Its late and my brain hurts

Reply | Quote

Replies

Open your query in design view.

Click on the Column Heading (Crosstab Row) and open Properties.

In Column Headings, type in the dates in the order you want, as in “11/01″,”12/01″,”01/02″,”02/02”, etc. Include the quotes (maybe single quotes, to keep things consistent in your query).

HTH,

Tom

Reply | Quote

Tom,
Should have stated that these aren’t static values. Every month a new month is added and the earliest month drops off.
Is there a way to automatically fill the column headings?
Scott

Reply | Quote

Scott,

Attached is an Access 97 db that shows how to use a rolling crosstab report – it seems to do what you want.

This one is designed for twelve months of data, but you can modify it to meet your needs.

HTH,

Tom

Reply | Quote

Thanks Tom,
I will use that if I can’t figure this one out. I already have the dynamic report built.
Just need to sort it correctly
Thanks

Reply | Quote

Add the following to the query: Year([YourTbl].[DateField])*12+DatePart(“m”,[YourTbl].[DateField])-1 and order by that

Reply | Quote

Rich,
That sorted the colums correctly but the column labels are 24021 24022 24023 24024 24025 24026
not the mm/yy
Scott

Reply | Quote

Don’t output the field I gave you, just set it to Where on the total field

Reply | Quote

Rich,
When I try to sort on that field I get this message

Reply | Quote