CODEWITHSUNDEEP
sqlms-access
Jolien .A
Jolien .A

Reputation: 173

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.

Upvotes: 1

Views: 2423

Answers (4)

rob smith
rob smith

Reputation: 1

ive found a way of ordering it in the correct order. in the report containing the graph, make the recordsource your aggregate query. Add two expression fields in the query one for the year using YEAR(DateField) and the second for the month using MONTH(DateField).

in the report sort by the two expressions month within year and it comes out in the correct Gregorian Calendar order.

Upvotes: 0

rob smith
rob smith

Reputation: 1

I tried the other suggesions on an aggregate totals by month report and no luck. the only way i could get the actual month labels was by putting labels directly beneath the chart, which means altering it every month!

Upvotes: 0

LiamH
LiamH

Reputation: 1502

I would add the following to your query, after your GROUP BY clause:

ORDER BY [datestamp] ASC;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

Related Questions