Chart

[jmuirman1]

Made a chart from a query. Months are out of order. But data in q is organized ascending - seems like the chart should follow. Any suggestions? Thanks,



john

[isladogs]

Are the months in alphabetical order?
If so, you need to sort by month number

[June7]

Show the chart RowSource SQL. If it is a CROSSTAB, you can specify the column order by specifying column headings http://allenbrowne.com/ser-67.html#ColHead

I just did a test creating a bar chart with dates on the x-axis. Even though displaying month as MMM, they are in chronological order (May, Jun, Jul, Aug). The RowSource is a CROSSTAB created by the wizard. Doesn't even specify columns.

TRANSFORM Count(Games.[Plate]) AS CountOfPlate
SELECT (Format([StartDate],"mmm"" '""yy")) AS Expr1
FROM Games
GROUP BY (Year([StartDate])*12+Month([StartDate])-1), (Format([StartDate],"mmm"" '""yy"))
PIVOT Games.[PlayField];

[jmuirman1]

I placed the query in ascending order by month number - still shows months in alphabetical order.

here is my row source: SELECT [month],Sum([CountOfID]) AS [SumOfCountOfID] FROM [2018qnew cases chart for 2018] GROUP BY [month];

any suggestions?

thanks,

John

[June7]

AFAIK, record order has no bearing on the order displayed in chart when using a simple SELECT sql as the chart data source.

The data must be month number, not month name.

There is no ORDER BY clause in that sql anyway.

[jmuirman1]

No way to show the months in alphabetical order? That doesn’t seem right. Is there some other way to accomplish this?

[isladogs]

You sort in month number order but display the month names

This applies to both crosstab and non-crosstab queries used for charts

For example this (non-crosstab) SQL sorts by year number and month number and displays months formatted as 'mmm yyyy' e.g. Feb 2018

SELECT (Format([UsageDate],"mmm yyyy")) AS [Month], Sum(tblAccountUsage.UsageCount) AS TotalFROM tblAccountUsage
GROUP BY (Format([UsageDate],"mmm yyyy")), (Year([UsageDate])*12+Month([UsageDate])-1)
ORDER BY (Year([UsageDate])*12+Month([UsageDate])-1);

produces this chart



Hopefully you can see how its done

[June7]

So sort order does matter. Learned something new about charts, thanks Colin.

[isladogs]

You're welcome June