asked on

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

Gustav Brock

Modify the query to include the numeric month:

MonthNo: Month([datefield])

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

/gustav

DataTransformationServices

ASKER

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.

Mike Eghtebas

For sort purpose, use

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

Gustav Brock

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

Jeffrey Coachman

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")

DataTransformationServices

ASKER

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.

Gustav Brock

That can't be true.
Which expression did you use?

/gustav

Jeffrey Coachman

Again, ...what is your question here?

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

Jeff

Gustav Brock

It is sorting by the numeric month, while displaying the month name.

/gustav

Jeffrey Coachman

OK

Thanks

Jeff

DataTransformationServices

ASKER

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.

Jeffrey Coachman

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

Gustav Brock

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

DataTransformationServices

ASKER

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

Mike Eghtebas

Without Form1, it is a bid dificult to test it.

Did you try:

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

?

Jeffrey Coachman

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

GRayL

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

GRayL

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.

ASKER CERTIFIED SOLUTION

datAdrenaline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DataTransformationServices

ASKER

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!!

DataTransformationServices

ASKER

THANK YOU ALL so much, that worked perfectly!