Sort months by calendar order from a crosstab query

[Dmackcwby]

I'm trying to get a report setup to show me data in the in the following
manner:

Month data 1 data 2 data 3 data 4
Jan 2 5 4 9
Feb 5 8 7 9
Mar 8 1 5 10
Apr 2 4 5 8

The problem is that the months are being displayed in alphabetical order
rather than calendar order. When I run the crosstab query, the months
display in calendar order. Here is the SQL for the query:

TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT Format([Date],"mmmm") AS Expr1, Count(tblFsLog.LogID) AS CountOfLogID1
FROM tblFsReason INNER JOIN tblFsLog ON
tblFsReason.FsReasonID=tblFsLog.ReasonID
GROUP BY Month(Date), Format([Date],"mmmm")
ORDER BY Month(Date)
PIVOT tblFsReason.Reason;

How do I get the report to display the month in calendar order?

[Al Campagna]

Dmackcwby,
Create a calculated column in your report query...
MonthNo : Month(YourDateField)
Now group on that field... rather than the text Month.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Dmackcwby" <Dmac...@discussions.microsoft.com> wrote in message
news:45B4AF87-B64B-43C7...@microsoft.com...

[Dmackcwby]

Thank you so much for your help. I added the field and every thing works
great.

[chenzh...@gmail.com]

Hi, I am also facing this problem. I do not get it. How to calculate it by calender oder again? Thank you.

[brian.g...@gmail.com]

I am having the same issue, but I'm using case statements to get the month names.:

SELECT Category, COUNT(Category) AS CatCount,
(CASE WHEN Month(OpenDate) = 1 THEN 'January'
WHEN Month(OpenDate) = 2 THEN 'February'
WHEN Month(OpenDate) = 3 THEN 'March'
WHEN Month(OpenDate) = 4 THEN 'April'
WHEN Month(OpenDate) = 5 THEN 'May'
WHEN Month(OpenDate) = 6 THEN 'June'
WHEN Month(OpenDate) = 7 THEN 'July'
WHEN Month(OpenDate) = 8 THEN 'August'
WHEN Month(OpenDate) = 9 THEN 'September'
WHEN Month(OpenDate) = 10 THEN 'October'
WHEN Month(OpenDate) = 11 THEN 'November'
WHEN Month(OpenDate) = 12 THEN 'December' END) AS mopendate
FROM Cloud_Tickets
WHERE (YEAR(OpenDate) = '2013')
GROUP BY MONTH(OpenDate), Category
ORDER BY MONTH(OpenDate)

Also, if there is a better way to do this, please enlighten me. I am a novice to programming.

Thank you

Brian

[lilyb...@gmail.com]

On Thursday, April 10, 2008 2:34:02 AM UTC+5:30, Dmackcwby wrote:
> I'm trying to get a report setup to show me data in the in the following manner:Month data 1 data 2 data 3 data 4Jan 2 5 4 9 Feb 5 8 7 9Mar 8 1 5 10Apr 2 4 5 8The problem is that the months are being displayed in alphabetical order rather than calendar order. When I run the crosstab query, the months display in calendar order. Here is the SQL for the query:TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID SELECT Format([Date],"mmmm") AS Expr1, Count(tblFsLog.LogID) AS CountOfLogID1 FROM tblFsReason INNER JOIN tblFsLog ON tblFsReason.FsReasonID=tblFsLog.ReasonID GROUP BY Month(Date), Format([Date],"mmmm")ORDER BY Month(Date) PIVOT tblFsReason.Reason;How do I get the report to display the month in calendar order?

wow Thank u Bian....i was breakin my head for this....this works awesome
thank u again:)