Others

Sort Months Chronologically in a Microsoft Access Crosstab Query

Steve KSteve K Nov 20, 2026 868 views

Question details

Arrange month names in chronological order in an Access crosstab query.

Sort Months Chronologically in a Microsoft Access Crosstab Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A crosstab query displays results by quarter and month using a date field.
Observed behavior
Month names may appear alphabetically instead of in calendar order.
Before you start

Identify the date field and check whether months appear as column headings or row values. If the data spans multiple years, decide whether to filter to one year or group results by year.

Solution 1Recommended

Set a Fixed Month Order in the Crosstab PIVOT Clause

Use a fixed list when month names are crosstab column headings and need to appear from January through December.

In an Access crosstab query, ORDER BY sorts result rows; it does not set the order of pivot column headings. A PIVOT IN list specifies the heading order explicitly.

The example below groups rows by category and quarter. Replace YourTable, Category, Amount, and NewReportDate with your actual table and field names.

1
Open SQL View

In Access, open the crosstab query, then select View > SQL View.

2
Specify the month headings

Adapt this example to your query: TRANSFORM Sum([Amount]) SELECT [Category], DatePart("q", [NewReportDate]) AS [Quarter] FROM [YourTable] WHERE [NewReportDate] Is Not Null GROUP BY [Category], DatePart("q", [NewReportDate]) ORDER BY [Category], DatePart("q", [NewReportDate]) PIVOT Format([NewReportDate], "mmmm") IN ("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");

3
Run the query

Select Run and check that the month columns follow calendar order and quarter rows are sorted numerically.

Set a Fixed Month Order in the Crosstab PIVOT Clause
Check your date range and month names: The fixed list shows all 12 month columns, even when some have no data. Match the list to the month names produced by your system's locale. To avoid combining the same quarter and month across years, filter the date range or add the year as a grouped row field.
Free Microsoft Office alternative

Review Exported Access Results in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with exported Access results. Export the query to Excel from Access, then open it in WPS Spreadsheets for further review and sorting; WPS does not change the Access query itself.

Review and sort exported crosstab data in a spreadsheetOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does ORDER BY not rearrange my month columns?

In an Access crosstab query, ORDER BY sorts the result rows. To control month column headings, add a fixed list in the PIVOT clause, such as IN ("January", "February", "March").

How can I sort months when they are row values instead?

Include the numeric month expression in the query's grouped row fields, then use ORDER BY on the quarter and Month([NewReportDate]). Add the year first if results span multiple years.

Will months with no records still appear?

Yes. Month names listed in the PIVOT IN clause appear as columns even if there are no matching records; their result cells can be empty.

Can I open my Access crosstab results in WPS Office?

Yes. In Access, export the query results to an Excel file using the External Data export options, then open the XLSX file in WPS Spreadsheets. This does not modify the original Access query.