Cannot get months to sort on chart in chronological order if using month name.

I am creating a line graph in MS Access.  Attached is a sample of what the chart looks like.  It all works fine except I want the months to print using the name of the month, not the number.  I needed to number for it to print in chronological order.  There is another question on this forum which recommended using the Format function in Access.  That sort of worked in that it sorted the months on the graph properly but for some reason, the only rows that it would work for are the rows where there was monthly data in both of the years I was comparing.    Below is my initial query called IncidentTrendsTotalIncByMonthChart.   The results of it are then used in the crosstab query listed after this:
PARAMETERS [Forms].IncidentTrendsMenu![txtMonth] Short, [Forms].IncidentTrendsMenu![txtYear1] Short, [Forms].IncidentTrendsMenu![txtYear2] Short;
SELECT IncidentYear, IncidentMonth, Count(IncidentID) AS TotalIncidentByMonth
FROM IncidentTrendsDetail
WHERE (((IncidentYear)=[Forms].[IncidentTrendsMenu]![txtYear1])) OR (((IncidentYear)=[Forms].[IncidentTrendsMenu]![txtYear2]) AND ((IncidentMonth)<=[Forms].[IncidentTrendsMenu]![txtMonth]))
GROUP BY IncidentYear, IncidentTrendsDetail.IncidentMonth
ORDER BY IncidentYear, IncidentMonth;

 Below is the query that creates the graph:
TRANSFORM Sum(TotalIncidentByMonth) AS SumOfTotalIncidentByMonth
SELECT IncidentMonth
FROM IncidentTrendsTotalIncByMonthChart
GROUP BY IncidentMonth
PIVOT IncidentYear

Any help would be most appreciated.
 IncidentTrendsNumIncByMonth.pdf
DataTransformationServicesAsked:
Who is Participating?
 
datAdrenalineConnect With a Mentor Commented:
I think the others have covered it, but my eyes may be going buggy by this late hour -- also hindered by a small font on my laptop ---  The following works fine for me as the SQL for the source of your chart:

TRANSFORM Sum(IncidentTrendsTotalIncByMonthChart.TotalIncidentByMonth) AS SumOfTotalIncidentByMonth
SELECT MonthName([IncidentMonth],True) AS iMonth
FROM IncidentTrendsTotalIncByMonthChart
GROUP BY IncidentTrendsTotalIncByMonthChart.IncidentMonth, MonthName([IncidentMonth])
ORDER BY IncidentTrendsTotalIncByMonthChart.IncidentMonth, IncidentTrendsTotalIncByMonthChart.IncidentYear
PIVOT IncidentTrendsTotalIncByMonthChart.IncidentYear;

Which, is similar to GRayL's, with a different sort.


0
 
Gustav BrockCIOCommented:
Modify the query to include the numeric month:

MonthNo: Month([datefield])

and sort on that.
Don't sort on the month name (IncidentMonth)

/gustav
0
 
DataTransformationServicesAuthor Commented:

Thank you for your reply.  The column IncidentMonth is a numeric column.  If I use the Format command on that, it only pulls rows for years that have monthly data in them.  You'll notice in my graph that 2011 only has 2 months worth of data whereas 2010 has a full year.  If I use the Format command on the IncidentMonth column, the query only retrieves January and February for both years.  Very strange.  
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Mike EghtebasDatabase and Application DeveloperCommented:
For sort purpose, use

SortField: format([datefield],"yyyymmdd")

0
 
Gustav BrockCIOCommented:
Oh, didn't study the graph, sorry.

You may be able to add a column:

MonthName: Format(DateSerial(IncidentYear, IncidentMonth, 1), "mmmm")

Keep the existing columns and your current sorting.

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
So what is your *one* question here?

How to sort the months?
... or showing all months if no data is present?

If you want to show all months then you have to either left join to a "Months" table, ...
...or hardcode the months in the Crosstab SQL  using something like this:

PIVOT Format([YourDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
0
 
DataTransformationServicesAuthor Commented:
Thank you both, I have tried both things and for some reason, when using the Format command the only months data that I get back is December and January for 2010 and 2011.   There is data for all the months for 2010.  This is a tough one.
0
 
Gustav BrockCIOCommented:
That can't be true.
Which expression did you use?

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again, ...what is your question here?

Sorting or showing all month?
Please clarify.....


Jeff
0
 
Gustav BrockCIOCommented:
It is sorting by the numeric month, while displaying the month name.

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK

Thanks

Jeff
0
 
DataTransformationServicesAuthor Commented:
My one question is how do I get the name of the month to print on the graph while still listing them in chronological order.  The first query (IncidentTrendsTotalIncByMonthChart produces the following results:
IncidentYear IncidentMonth  TotalIncidentByMonth
2010      1               53
2010      2               45
2010      3               40
2010      4               45
2010      5               47
2010      6               50
2010      7               53
2010      8               52
2010      9               39
2010      10               49
2010      11               20
2010      12               28
2011      1               36
2011      2               28

That works fine.  The results of the above query are used as the source for the crosstab query.  That crosstab query produces the following:
IncidentMonth  2010      2011
1            53      36
2            45      28
3            40      
4            45      
5            47      
6            50      
7            53      
8            52      
9            39      
10            49      
11            20      
12            28      

All is well there too.  Now, I want to replace the numeric month number with the name of the month.  

The column IncidentMonth is a numeric column which I have for sorting.  However, if I try to use the Format command on that column to give me the name of the month, the following are the results:
IncidentYear      UsingFormatCmd      TotalIncidentByMonth
2010      Dec                                       53
2010      Jan                                      468
2011      Dec                                       36
2011      Jan                                        28

The command I used was : format([IncidentMonth],"mmm") in the first query IncidentTrendsTotalIncByMonth.  However, the same exact thing happens if I use the Format command in the crosstab query.  These numbers here don't make any sense.  The column IncidentMonth is a numeric column.
0
 
Jeffrey CoachmanMIS LiasonCommented:
One way around this is to pull the month (Number), and the MonthName ( (abbreviated.) out of the source data in a query, then create the crosstab (or the chart) from the query, then sort on the Month (number), but display the MonthName (abbreviated)

I know there are other ways to do this...
For me, I like to pull out "anything" that might be useful in a query, this way I can have this data available no matter what I do after that.

Then create the the chart from this query.
The chart may default to sorting by the monthName, but you can easily change this in the Rowsource to bee the MonthNumber.

 
In your case it is not clear what came first, The Chart, a query, The Crosstab query, or something else entirely...?
Can you post a sanitized sample version of this DB, that illustrates this issue?

Certainly what cactus_data posted should work for you.

;-)

JeffCoachman
0
 
Gustav BrockCIOCommented:
Yes, it's your crosstab that introduces the issue, not Format or anything else.
You could try pulling the date of the month to the crosstab (DateSerial ...) and then - in the crosstab or in the graph - format that to the year and month name only (datValue, "yyyy mmm").

I don't use Access' graph utils so I cannot advice in details.

/gustav
0
 
DataTransformationServicesAuthor Commented:
Thank you again.  I tried what you had recommended but could not get it to work.  Still it sorted the months on the graph by month name instead of month number.  I am attaching a sample db.  Double click on the report IncidentTrendsNumIncByMonth.  When it prompt you for a month, enter 2.  Enter 2010 for the Year1 prompt and 2011 for the Year2 prompt.  Thanks again for looking into this. Incidents.accdb
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Without Form1, it is a bid dificult to test it.

Did you try:  

SortField: format([datefield],"yyyymmdd")

?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Try this as the chart Rowsource:

TRANSFORM Sum(IncidentTrendsTotalIncByMonthChart.TotalIncidentByMonth) AS SumOfTotalIncidentByMonth
SELECT Format(First(IncidentTrendsTotalIncByMonthChart.MonthName),"mmm") AS MonthName
FROM IncidentTrendsTotalIncByMonthChart
GROUP BY IncidentTrendsTotalIncByMonthChart.IncidentMonth
ORDER BY IncidentTrendsTotalIncByMonthChart.IncidentMonth
PIVOT IncidentTrendsTotalIncByMonthChart.IncidentYear;

May be a bit "Brute-Force-ish", but it gets you the output you requested...

You will have to put your Parameter back in though...
Access-EEQ26911764ChartSortMonth.accdb
0
 
GRayLCommented:
Change this:

TRANSFORM Sum(TotalIncidentByMonth) AS SumOfTotalIncidentByMonth
SELECT IncidentMonth
FROM IncidentTrendsTotalIncByMonthChart
GROUP BY IncidentMonth
PIVOT IncidentYear

to:

TRANSFORM Sum(TotalIncidentByMonth) AS SumOfTotalIncidentByMonth
SELECT MonthName(IncidentMonth,True)
FROM IncidentTrendsTotalIncByMonthChart
ORDER BY IncidentMonth
GROUP BY IncidentMonth
PIVOT IncidentYear
0
 
GRayLCommented:
DataTransformationServices:  You got to get a shorter handle ;-)  I only have A2003 so I cannot continue to help unless you can upload a .mdb file.  I have a few ideas, but I need to see the structure of IncidentTrendsDetail.  BTW your version of Access can create an mdb by clicking Tools, Database Utilities, Convert Database.
0
 
DataTransformationServicesAuthor Commented:
thank you so much - I will try them all.  I haven't responded because I have not been able to sign onto my client's server; there is some problem on their end and I hope to get on today.   I will let you know asap.  Thank you all again!!
0
 
DataTransformationServicesAuthor Commented:
THANK YOU ALL so much, that worked perfectly!
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.