?
Solved

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

Posted on 2011-03-25
21
Medium Priority
?
1,530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +3
21 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35216395
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
ID: 35216886

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 34

Expert Comment

by:Mike Eghtebas
ID: 35217002
For sort purpose, use

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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35217027
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
ID: 35217078
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
ID: 35217146
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 51

Expert Comment

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

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35217273
Again, ...what is your question here?

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


Jeff
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35217316
It is sorting by the numeric month, while displaying the month name.

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35217378
OK

Thanks

Jeff
0
 

Author Comment

by:DataTransformationServices
ID: 35217495
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
ID: 35217560
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 51

Expert Comment

by:Gustav Brock
ID: 35217655
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
ID: 35218043
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 34

Expert Comment

by:Mike Eghtebas
ID: 35218468
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
ID: 35218746
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
ID: 35223000
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
ID: 35223518
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 500 total points
ID: 35238344
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
ID: 35239940
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
ID: 35342737
THANK YOU ALL so much, that worked perfectly!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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