Jolien .A Jolien .A - 1 year ago 86
SQL Question

Ms ACCESS and queries: dates in graph not in order

I use queries in Ms ACCESS to create graphs (shown in forms) to represent monthly spend data on a supplier. I want the x axis to be the months in chronological order, and this is where I'm having issues.
enter image description here

The picture above shows that the x axis starts with april 2016, although the earliest date is august 2015.

The query code that creates the graph is the following:

SELECT (Format([DateStamp],"mmm"" '""yy")) AS Expr1, Sum([Item Master].SpendPerMaterial) AS Expr2
FROM [Item Master]
WHERE ((([Item Master].SupplierName)=[Forms]![Supplier History]![List0]))
GROUP BY (Format([DateStamp],"mmm"" '""yy")), (Year([DateStamp])*12+Month([DateStamp])-1);

[Item Master] is the table were all data is retrieved from. DateStamp refers to the column with months, SpendPerMaterial is the spend of a certain material in that month (which is aggregated since we look at the supplier level, not the material level), and List0 is a list where users can select a supplier from a list of suppliers.

Answer Source

You should never rely on the ordering of results from a query unless you include an explicit order by. In your case, the results are ordered by the columns alphabetically (because of the group by).

You can fix this by adding:

order by max([DateStamp])

to the query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download