I want to sort the columns of a crosstab query that is displaying date values formated "mmm yy" and covers data from from that one year.

I want the sort to be based on time--earliest month and year first and latest month and year last. It seems that queries always sort by alpha numeric display so that Aug will always be the first column even if it isn't the first month of data.

Also, ColumnHeadings property won't work as the start and end dates of the query will regularly change.

Any suggestions?

Thanks.

I am unsure but have you tried the In statement for the crosstab? This forces columns to be present even if there is no data, i am unsure if it forces a sort of the columns as well...

Regards

The Mailman

I'm having the same exact problem, but my date range is from one year ago to today.

Can you please explain the IN Statement some more?

Thanks!

Add a predefined list to your IN clause.

An access pivot query goes kindoff like so:
Transform
Select
From
Where
group by
Pivot
In ()

Simply fill in the "In" clause

The problem arises when the range of data selected crosses a year. If you stick with month as the grouping, data from multiple years will get added together. If you want to include year, in the heading to prevent this grouping, you can't use a text month since that will cause the alpha sort. You will either need to show years on separate rows or use numeric month values in the heading.

TRANSFORM Count(Table1.keya) AS CountOfkeya
SELECT Table1.numDC, Count(Table1.keya) AS [Total Of keya]
FROM Table1
GROUP BY Table1.numDC
PIVOT Format([paydt],"yyyy/mm");
or
TRANSFORM Count(Table1.keya) AS CountOfkeya
SELECT Table1.numDC, Year(Table1.paydt) As PayYear, Count(Table1.keya) AS [Total Of keya]
FROM Table1
GROUP BY Table1.numDC, Year(Table1.paydt)
PIVOT Format([paydt],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");