Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1685
  • Last Modified:

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

0
smagnus1
Asked:
smagnus1
  • 9
  • 2
  • 2
  • +1
1 Solution
 
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
Format([Date],"yyyy mm")

if multiple years involved.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Mike EghtebasDatabase 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
 
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 9
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now