Others

How to Sort Access Query Results by Month and Year Chronologically

Phi Hung Vo Sep 28, 2026 869 views

Question details

The user needs to sort an Access make-table query chronologically by month and year, rather than alphabetically by the month's name.

How to Sort Access Query Results by Month and Year Chronologically

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a make-table query that outputs concatenated month and year values from September 2024 to December 2030.
Observed behavior: The query sorts all records by the month name (e.g., grouping all January records together) instead of listing them in true chronological order.

Before you start

Ensure your underlying data contains separate numerical fields for the Year (e.g., 2024) and the Month (e.g., 1 for January, 2 for February) so you can sort the records mathematically rather than alphabetically.

Solution 1Recommended

Use Year and Month ID Fields for Ascending Sort

By applying a primary sort on a numerical Year field and a secondary sort on a numerical Month field, you can force Access to order the concatenated text strings chronologically.

When you concatenate Month and Year into a single text string (like 'January 2024'), Access treats the result as text and sorts it alphabetically. To fix this, you must apply the sort operation to the underlying numerical IDs instead of the concatenated text.

Open Query Design View

Right-click your query in the navigation pane and select 'Design View'.

Add Year-ID Field

Drag the numerical Year-ID field into the query grid. Place it as the first sorting column on the left.

Add Month-ID Field

Drag the numerical Month-ID field into the query grid directly to the right of the Year-ID field.

Set Sort Order

In the 'Sort' row for both the Year-ID and Month-ID columns, select 'Ascending' from the dropdown menu.

Hide Sort Columns

If you do not want these numerical IDs to appear in your final make-table query, uncheck the 'Show' box for both columns.

Use Year and Month ID Fields for Ascending Sort

Correct Chronological Order: Access will now sort by year first, and then sequentially arrange the months within each year, outputting your records correctly.

Free Microsoft Office alternative

Manage Data Easily with WPS Spreadsheet

While Microsoft Access is a dedicated database tool, many users prefer managing lists, dates, and concatenated strings using spreadsheets. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office, complete with powerful data sorting and filtering features.

1. Download and Install: Get WPS Office for free and open WPS Spreadsheet.
2. Open Data File: Import your exported database records or open your existing spreadsheet.
3. Apply Custom Sort: Highlight your data, navigate to the Data tab, and use the Custom Sort feature to order by Year and then Month chronologically.

Free, lightweight, and fast-loadingSeamlessly compatible with Microsoft Excel (.xlsx) formatsAdvanced data sorting and pivot table features built-inFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my query sort all January records together?

When a query concatenates months and years into a text string, the database applies an alphabetical sort rather than recognizing it as a date. Therefore, 'January' always comes before 'September' regardless of the year.

How can I hide the sorting columns in the final table?

In the Access Query Design view, simply uncheck the 'Show' box in the grid beneath the Year-ID and Month-ID columns. They will still control the sort order but won't be visible in the final output.

Can I sort dates chronologically using a single field?

Yes, if your field is formatted as an actual Date/Time data type rather than a concatenated text string, setting the sort order to ascending will automatically arrange the records chronologically.

Office Quick Solutions