CODEWITHSUNDEEP
ms-access
Lucian Bumb
Lucian Bumb

Reputation: 2881

How to sort by month name in access pivot column?

I have the following pivot query : 

TRANSFORM Sum(Forecast.Openquantity) AS OpenQty
SELECT Forecast.Material, Forecast.Description, Forecast.Unrestricted
FROM Forecast
GROUP BY Forecast.Material, Forecast.Description, Forecast.Unrestricted
ORDER BY Forecast.Material, Forecast.Description, MonthName(Month([Forecast].[LoadingDate]))
PIVOT MonthName(Month([Forecast].[LoadingDate]));

which works fine, but the Month Name (column row) are sort alphabetically, not by month number.

The above query give me the columns in this order: April, August, December, February ...

and I want the normal order for months: January, February, March ...

How can I change this query in order to have correct sort for month name columns?

Upvotes: 0

Views: 736

Answers (1)

Gustav
Gustav

Reputation: 55806

You do it like this:

PIVOT MonthName(Month([Forecast].[LoadingDate])) IN ("January","February", ... ,"December");

Upvotes: 2

Related Questions