Others

How to Preserve UNION Query Order in Microsoft Access

Khadija Khan Oct 21, 2026 868 views

Question details

The user needs to explicitly control and preserve the sequence of combined datasets when executing a UNION query in Microsoft Access.

How to Preserve UNION Query Order in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Combining multiple datasets using a UNION query while ensuring the final results are displayed in a specific, predictable order rather than an automated sorted order.
Observed behavior: Microsoft Access automatically sorts the combined data to remove duplicate rows during a standard UNION query, which overrides the original sequence of the datasets.

Before you start

Determine whether your query strictly requires filtering out duplicate records, as this dictates whether you should use UNION or UNION ALL.

Solution 1Recommended

Add a Numeric Sort Column and Use ORDER BY

The most reliable way to enforce a specific order in a UNION query is to manually inject a custom sorting column into each SELECT statement and sort by it.

By default, Microsoft Access does not guarantee the display order of a UNION query. To gain complete control over the sequence, you must define a custom numeric field in each part of the query and apply an ORDER BY clause at the very end of the entire SQL statement.

Open SQL View

Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, click on 'Query Design', close the 'Show Table' dialog, and switch to 'SQL View' using the view toggle at the bottom right or top left.

Add a Sort Column to the First Query

In your first SELECT statement, add a hardcoded numeric value as a new column with an alias (e.g., SELECT 1 AS SortBy, FieldName FROM TableName).

Add Sort Columns to Subsequent Queries

In your following SELECT statements, increment the hardcoded numeric value sequentially (e.g., SELECT 2 AS SortBy, FieldName FROM TableName).

Apply the ORDER BY Clause

At the very end of the complete UNION query, add the clause ORDER BY SortBy; to instruct Access to sort the final combined result based on your custom numeric column.

Add a Numeric Sort Column and Use ORDER BY

Use UNION ALL When Possible: If you do not need to remove duplicate records, always use 'UNION ALL' instead of 'UNION'. It prevents the database from performing an automatic duplicate-removal sort and runs significantly faster.

Solution 2

Change UNION to UNION ALL to Prevent Unwanted Sorting

If your main issue is that the standard UNION operator is altering your data order to remove duplicates, switching to UNION ALL bypasses this behavior.

Free Microsoft Office alternative

Upgrade Your Daily Productivity with WPS Office

While Microsoft Access handles heavy relational databases, managing everyday data analysis, sorting, and reporting is often far easier in a dedicated spreadsheet tool. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, ensuring seamless migration for all your Word, Excel, and PowerPoint files.

1. Download the Installer: Visit the official WPS Office website and click the free download button for your specific operating system.
2. Install WPS Office: Run the downloaded installer file and follow the quick on-screen setup instructions.
3. Open Your Data Files: Launch WPS Spreadsheet to instantly open your existing Excel workbooks and CSV files exported from Access, with all formatting preserved.

Seamless format compatibility with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx).Advanced sorting, filtering, and data manipulation tools in WPS Spreadsheet for handling exported database records.Lightweight application that uses minimal system resources and runs smoothly on multiple platforms.Familiar, intuitive user interface that requires zero learning curve for users switching from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Microsoft Access automatically change the sort order of my UNION query?

When you use the standard UNION operator, Microsoft Access inherently sorts the combined data in order to scan for and remove any duplicate records. This underlying process alters the display order of your final dataset regardless of the original data entry sequence.

What is the primary difference between UNION and UNION ALL?

UNION combines the results of multiple SELECT statements and removes duplicate rows, which requires an internal sort mechanism. UNION ALL combines the datasets but retains all duplicate records, executing faster and appending data without enforcing that internal sort.

Can I put an ORDER BY clause inside each individual SELECT statement of a UNION query?

No, Microsoft Access SQL does not allow an ORDER BY clause within the individual SELECT statements that make up a UNION query. You must define a single ORDER BY clause at the very end of the entire SQL statement to sort the combined results.

How do I sort by multiple fields in an Access UNION query?

You can sort by multiple fields by listing them in the final ORDER BY clause at the end of the query, separated by commas. For example: ORDER BY SortBy ASC, CategoryName DESC;

Office Quick Solutions