UNION Queries in Access: Combining Data from Multiple Sources
UNION queries stack results from multiple SELECT statements into a single result set. Learn when and how to use them effectively in Microsoft Access.
A UNION query combines the results of two or more SELECT statements into a single result set. Where a JOIN combines columns from multiple tables horizontally, a UNION combines rows from multiple queries vertically — stacking them on top of each other.
When to Use UNION Queries
UNION queries are the right tool when you need to:
- Combine data from two tables with the same structure (e.g., current year orders + archived orders)
- Create a single list from multiple sources (e.g., customers + suppliers + employees for a contact list)
- Add summary rows to a detail query (e.g., append a "Total" row at the bottom)
- Build a combo box that shows options from multiple tables
Basic UNION Syntax
SELECT CustomerID, FirstName, LastName, 'Customer' AS Type
FROM Customers
UNION
SELECT SupplierID, ContactFirst, ContactLast, 'Supplier' AS Type
FROM Suppliers;
Rules for UNION queries:
- Both SELECT statements must return the same number of columns
- Corresponding columns must have compatible data types
- Column names in the result come from the first SELECT statement
- UNION removes duplicate rows by default
UNION vs. UNION ALL
UNION removes duplicate rows — it performs a sort and deduplication step, which is slower.
UNION ALL keeps all rows including duplicates — it is faster because no deduplication is needed.
Use UNION ALL when:
- You know there are no duplicates (different tables, different IDs)
- You want to keep duplicates intentionally
- Performance matters and duplicates are acceptable
SELECT OrderID, OrderDate, Amount FROM Orders_2025
UNION ALL
SELECT OrderID, OrderDate, Amount FROM Orders_2026;
Sorting UNION Results
You can only have one ORDER BY clause in a UNION query, and it must appear at the very end:
SELECT CustomerID, LastName, FirstName FROM Customers
UNION ALL
SELECT SupplierID, ContactLast, ContactFirst FROM Suppliers
ORDER BY LastName, FirstName;
The ORDER BY applies to the combined result set, not to individual SELECT statements.
Adding a Literal Column
A common pattern is adding a literal value to identify which source each row came from:
SELECT EmployeeID AS PersonID,
FirstName & ' ' & LastName AS FullName,
'Employee' AS PersonType,
Email
FROM Employees
UNION ALL
SELECT ContactID,
FirstName & ' ' & LastName,
'Contact',
Email
FROM Contacts
ORDER BY FullName;
Adding a Totals Row
One of the most useful UNION patterns is appending a summary row to a detail query:
SELECT SalesPerson, Sum(Amount) AS TotalSales
FROM Orders
GROUP BY SalesPerson
UNION ALL
SELECT 'TOTAL', Sum(Amount)
FROM Orders
ORDER BY SalesPerson;
This produces a result with individual salesperson totals plus a grand total row at the bottom (or top, depending on sort order).
UNION Queries in Access: The SQL View Requirement
UNION queries cannot be created in the Access Query Designer's graphical view — you must write them in SQL View. To create a UNION query:
- Create a new query (Create → Query Design)
- Close the "Show Table" dialog without adding any tables
- Click View → SQL View
- Type your UNION query directly
- Save the query
UNION queries appear in the Navigation Pane with a special icon (two overlapping circles).
Using UNION Queries as Combo Box Sources
A common use case is populating a combo box with options from multiple tables. For example, a "Assign To" combo box that shows both employees and contractors:
SELECT EmployeeID AS PersonID, FirstName & ' ' & LastName AS Name, 'E' AS Type
FROM Employees WHERE Active = True
UNION ALL
SELECT ContractorID, FirstName & ' ' & LastName, 'C'
FROM Contractors WHERE Active = True
ORDER BY Name;
Set this as the combo box's Row Source (Row Source Type: Table/Query).
Performance Considerations
UNION queries can be slow on large tables because Access must process all SELECT statements and combine the results. To improve performance:
- Use
UNION ALLinstead ofUNIONwhen duplicates are not a concern - Add WHERE clauses to each SELECT to limit the rows processed
- Ensure the fields used in WHERE clauses are indexed
- Consider creating a make-table query to materialize the result if it is used frequently in reports
Limitations
- UNION queries are read-only — you cannot edit data through them
- Cannot be used as the record source for a form that needs to be editable
- The Query Designer cannot display them graphically — SQL View only
- Access has a practical limit of 50 SELECT statements in a single UNION query
Conclusion
UNION queries are an elegant solution for combining data from multiple sources into a single result set. The totals-row pattern alone is worth learning — it is one of the cleanest ways to add summary rows to a report without complex VBA. Master UNION and UNION ALL and you will have a powerful tool for any reporting scenario that spans multiple tables or requires combined result sets.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Working with union queries in Access? Schedule a free call and get expert help with your complex query requirements.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on Queries & SQL
Subqueries in Access SQL: Queries Within Queries
Subqueries let you use the result of one query inside another, enabling complex filtering and calculations that are impossible with simple JOINs.
Totals Queries in Access: GROUP BY, COUNT, SUM, and More
Totals queries aggregate your data into summaries — counts, sums, averages, and more. Learn how to build them correctly and avoid the common mistakes.
Crosstab Queries in Access: Turn Rows Into Columns
Crosstab queries transform your data from rows into a pivot-table-style grid. Learn how to build them in Access and when to use them effectively.