Sort months by calendar order from a crosstab query

  • Thread starter Thread starter Dmackcwby
  • Start date Start date
D

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?
 
Thank you so much for your help. I added the field and every thing works
great.

Al Campagna said:
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 said:
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?
Click to expand...
Click to expand...
 
Hi, I am also facing this problem. I do not get it. How to calculate it by calender oder again? Thank you.
 
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?
Click to expand...

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
 
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 thereport to display the month in calendar order?
Click to expand...

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