UNION Queries in Access: Combining Data from Multiple Sources

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:

1. Both SELECT statements must return the same number of columns
2. Corresponding columns must have compatible data types
3. Column names in the result come from the first SELECT statement
4. 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:

1. Create a new query (Create → Query Design)
2. Close the "Show Table" dialog without adding any tables
3. Click View → SQL View
4. Type your UNION query directly
5. 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 ALL instead of UNION when 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.