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
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
GROUP BY Query1.Area
ORDER BY Query1.CalculatedMonth ASC
and the report - see attachment