Crosstab query sort order by months and years

Hi all,

I am attempting to create a dynamic graph utilizing MS Access that involks a crosstab query.  See below.
My problem is, check out the legend / order of the graph by dates.  It simply isn't in order except by alphabetical order.  Also, before this can of worms is opened, I am trying to take into consideration the year rolling aka - November 2007, December 2007, January 2008 in that order - NOT January 2008, November 2007, December 2007.
The first query which prompts for the date:
 
PARAMETERS [Enter the Start Date:] DateTime, [Enter the End Date:] DateTime;
SELECT Format([Date],"mmmm"", ""yyyy") AS CalculatedMonth, JobsPerHour.Area, [Jobs]/[Worked] AS JPH, Format([Date],"yyyymm") AS SortedMonth
FROM JobsPerHour
WHERE (((JobsPerHour.Date)>=[Enter the Start Date:] And (JobsPerHour.Date)<=[Enter the End Date:]))
ORDER BY Format([Date],"yyyymm");
 
 
The crosstab query, which calls on this first query:
TRANSFORM Avg(Query1.JPH) AS AvgOfJPH
SELECT Query1.Area
FROM Query1
GROUP BY Query1.Area
ORDER BY Query1.CalculatedMonth ASC
PIVOT Query1.CalculatedMonth;
 
and the report - see attachment

Open in new window

smagnus1Asked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
Change Query1 to:

PARAMETERS [Enter the Start Date:] DateTime, [Enter the End Date:] DateTime;
SELECT Format([Date],"yyyy") & " " & Format([Date],"mm") & " (" & Format([Date],"mmm") & ")" AS CalculatedMonth, JobsPerHour.Area, [Jobs]/[Worked] AS JPH, Format([Date],"yyyymm") AS SortedMonth
FROM JobsPerHour
WHERE (((JobsPerHour.Date)>=[Enter the Start Date:] And (JobsPerHour.Date)<=[Enter the End Date:]))
ORDER BY Format([Date],"yyyymm");

If you prefer the following version instead.

Mike
Legend2.bmp
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
change it to:

Format([Date],"mm mmmm"", ""yyyy")
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Format([Date],"mm mmmm yyyy")

or just

Format([Date],"mm yyyy")
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mike EghtebasDatabase and Application DeveloperCommented:
Format([Date],"yyyy mm")

if multiple years involved.
0
 
smagnus1Author Commented:
Right sort order, but not a clean legend on the graph.  Any ideas?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
If possible at all, please upload a minimized version of your mdb for me to work on it at home.
0
 
Jeffrey CoachmanMIS LiasonCommented:
smagnus1,

Usually there is a way to manipulate the source data to display the chart tha way you want without the crosstab.

I too would like to see a sample dB

JeffCoachman
0
 
smagnus1Author Commented:
Fair enough.  My DB is attached.  For what it is worth, all data is randomized anyway, so nothing top secret here.  My report, oddly enough, is named "Report1".  This is an Access 2003 DB.  Thanks again.
ProductionRate.mdb
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
smagnus1Date,

First I will be able is monday morning. I whish I had this yesterday evening.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Change Query1 to:

PARAMETERS [Enter the Start Date:] DateTime, [Enter the End Date:] DateTime;
SELECT Format([Date]," yyyy"" Month: ""mm") AS CalculatedMonth, JobsPerHour.Area, [Jobs]/[Worked] AS JPH, Format([Date],"yyyymm") AS SortedMonth
FROM JobsPerHour
WHERE (((JobsPerHour.Date)>=[Enter the Start Date:] And (JobsPerHour.Date)<=[Enter the End Date:]))
ORDER BY Format([Date],"yyyymm");

If the attached display is acceptable.

Mike

untitled.bmp
0
 
Jeffrey CoachmanMIS LiasonCommented:
smagnus1,

Or...
Instead put the parenthesis around the Month Number (and not the Month  Name) to emphasize the Month Name instead of the month number.

SELECT Format([Date]," yyyy (mm) mmm") AS CalculatedMonth, JobsPerHour.Area, [Jobs]/[Worked] AS JPH, Format([Date],"yyyymm") AS SortedMonth
FROM JobsPerHour
ORDER BY Format([Date]," yyyy (mm) mmm"), Format([Date],"yyyymm");

<Or shorten the month number in brackets to  (m)
(one digit)
...to make the month number even more inconspicuous


But Points to eghtebas for coming up with this solution
;-)

Note: The issue here is that the crosstab query will "Sort" by the value.
So if the "Value" is 2008 Aug, that will be first.
If the "Value" is 2008 01, then that will be first.

So you must have the Value sorted by the Month Number in order to have it display in the chart in month order.
The Name of the month wiil have to be last.

JeffCoachman

untitled.JPG
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
smagnus1Date,

re:> Right sort order, but not a clean legend on the graph.  Any ideas?

Above this comment, you had your solution as:

Format([Date],"yyyy mm")

if multiple years involved.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
smagnus1Date,

Any feedback?
0
 
DataTransformationServicesCommented:
Hello - this answer helped me alot.  I have one deviation from it.  In my Query1, I pull more that one years worth of data.  So I have monthly counts for two different years.  One of the years has data through to February.  The other year has data counts for the entire year.  The format commands works beautifullly if there are monthly counts for BOTH years only.  Isn't that strange?  Any ideas?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.