Solved

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

Posted on 2011-03-25
21
1,337 Views
Last Modified: 2013-11-28
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
0
Comment
  • 6
  • 5
  • 5
  • +3
21 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:DataTransformationServices
Comment Utility

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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
For sort purpose, use

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

0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:DataTransformationServices
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That can't be true.
Which expression did you use?

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Again, ...what is your question here?

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


Jeff
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It is sorting by the numeric month, while displaying the month name.

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK

Thanks

Jeff
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:DataTransformationServices
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:DataTransformationServices
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Without Form1, it is a bid dificult to test it.

Did you try:  

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

?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 125 total points
Comment Utility
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
 

Author Comment

by:DataTransformationServices
Comment Utility
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
 

Author Closing Comment

by:DataTransformationServices
Comment Utility
THANK YOU ALL so much, that worked perfectly!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now