Date Sorting in Crosstab

  • Thread starter Thread starter dtoney
  • Start date Start date
D

dtoney

No matter which date format I choose, the results of my crosstab query are
sorted in alpha order! I verified the variable declaration in my table is
date/time & even tried to change the properties of the crosstab to reflect
the date format. Below is my SQL. Please tell me what I'm doing wrong!
Thanks!

SELECT allpmdata.SEV, Format([DATE_CLOSED],"yyyy-mmm") AS DteClosed,
Count(allpmdata.KEY_ID) AS CountOfKEY_ID, Count(allpmdata.KEY_ID) AS [Total
Of KEY_ID] INTO tblMonthly_By_Severity
FROM allpmdata
GROUP BY allpmdata.SEV, Format([DATE_CLOSED],"yyyy-mmm")
ORDER BY Format([DATE_CLOSED],"yyyy-mmm");
 
You are forcing the dteClosed to be a string by using the format function.
You can force the string to sort in desired order if you use a format of
"yyyy-mm".

Otherwise you can break the date into two number fields by using
Year(Date_Closed) and Month(Date_Closed)

Or force the date to be a true date - say the first of the month,
DateAdd("d",1-Day(Date_Closed),Date_Closed)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
That ain't a crosstab query!

In the crosstab query SQL Pivot section enter this --
PIVOT YourFormatinf IN("2009-JAN", "2009-FEB","2009-MAR", ...
,"2009-DEC");
 
Using the Format function changes the date to a string. Your mmm is going to
make it a short month and sort by it.

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'2009-JAN','2009-FEB','2009-MAR'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And it will create an empty
field even if there is no matching data.
 
Back
Top