Sort months by calendar order from a crosstab query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dmackcwby - 09 Apr 2008 22:04 GMT

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?

Reply to this Message

Al Campagna - 09 Apr 2008 22:18 GMT

Dmackcwby,
Create a calculated column in your report query...
MonthNo : Month(YourDateField)
Now group on that field... rather than the text Month.

Signature

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."

> I'm trying to get a report setup to show me data in the in the following
> manner:
[quoted text clipped - 19 lines]
>
> How do I get the report to display the month in calendar order?

Reply to this Message

Dmackcwby - 09 Apr 2008 22:51 GMT

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

> Dmackcwby,
> Create a calculated column in your report query...
[quoted text clipped - 23 lines]
> >
> > How do I get the report to display the month in calendar order?

Reply to this Message