Access 101: UNION Query

This article is part of my Quick Start Guide to Microsoft Access series, Access 101.

In the world of Microsoft Access, queries are the workhorses that power your database.

They allow you to retrieve, manipulate, and analyze your data in various ways. Among the different types of queries, the UNION query holds a unique place. This type of query allows you to combine the results of two or more SELECT queries into a single result set.

What is a UNION Query?

A UNION query is used to combine the results of multiple SELECT queries into a single dataset.

This can be incredibly useful when you need to pull together data from different tables or queries that share a similar structure. For example, if you have separate tables for actual past values and future budgeted values, a UNION query can help you create a single dataset to use as the record source for an Access report.

Basic Structure of a UNION Query

The basic structure of a UNION query is as follows:

SELECT field1, field2, ..., fieldN
FROM table1
UNION
SELECT field1, field2, ..., fieldN
FROM table2
UNION
SELECT field1, field2, ..., fieldN
FROM tableN;

Here’s a breakdown of the components:

SELECT field1, field2, ..., fieldN: This is the standard SELECT statement where you specify the fields you want to retrieve.
FROM table1: This specifies the first table you are querying.
UNION: This keyword combines the results of the first SELECT query with the second.
SELECT field1, field2, ..., fieldN: This is the second SELECT statement.
FROM table2: This specifies the second table you are querying.
UNION...FROM tableN: More than two SELECT statements may be combined in a single UNION query.

UNION vs. UNION ALL

A standard UNION query (as shown above) removes duplicate records.

A UNION ALL query (same syntax as above, but with UNION ALL in place of UNION) will not attempt to remove duplicate records.

In practice, I find that most of the time I structure my UNION queries in such a way that I know there will be no duplicate records (such as in my actual vs. budget example above). In such cases, using UNION ALL instead of UNION will improve performance because the database engine does not need to check for duplicates.

Simple Example of a UNION Query

Let’s consider an example where we have two tables: Budget and Actual. Both tables have the same structure with fields AcctID, MonthYear, and Amount.

SELECT AcctID, YearMonth, Amount, "Budget" As AmountType
FROM Budget
UNION ALL
SELECT AcctID, YearMonth, Amount, "Actual" As AmountType
FROM Actual;

This query will return a combined list of actual and projected account amounts by month and year.

Better Example of a UNION Query

While budget amounts may be entered by month and year, actual amounts in a typical accounting system will be entered by date, then grouped by month and year for reporting purposes. UNION queries handle this situation easily. Here's a more typical example of a UNION query, showing how two tables with different structures can still be combined into a single result set:

SELECT AcctID, YearMonth, 
       Amount, "Budget" As AmountType
FROM Budget
UNION ALL
SELECT AcctID, Year(ApplyDate) & "-" & Month(ApplyDate) AS YearMonth, 
       Sum(Amount), "Actual" As AmountType
FROM TransactionDetail
GROUP BY Year(ApplyDate), Month(ApplyDate);

QBE Window Won't Display UNION Queries

One of the restrictions of Access's so-called "Query-By-Example" (QBE) window, is that it does not allow you to visualize UNION queries:

The QBE Window: Training Wheels for Access Developers

If you’re new to relational databases, the QBE window is the solution to--and cause of--many of your query problems.

No Longer SetMike Wolfe

Therefore, to edit a UNION query in Access, you must first switch to SQL view.

As a bit of a workaround, you can use the QBE window to create the individual SELECT queries that will comprise your final UNION query. Then, your final UNION query–which still must be done in the text-only SQL view, can be as simple as this:

SELECT * FROM MyUnionPart1
UNION
SELECT * FROM MyUnionPart2
UNION
SELECT * FROM MyUnionPartN

Just be sure that the number of columns and their data types match among your various child SELECT queries.

Running a UNION Query in Access

To run a UNION query in Access, follow these steps:

Open your Access database and go to the "Create" tab on the Ribbon.
Click on "Query Design" to open a new query.
Use the query-by-example (QBE) window to design the first SELECT query.
Switch to SQL View by clicking on the "SQL View" button in the "Design" tab.
Go to the end of the SELECT statement, press [Enter], then type UNION or UNION ALL on the next line.
Press [Enter] and input the next SELECT statement.
Repeat steps 5 and 6 for any additional SELECT statements.

A Note About "Table-less" UNION Queries

Microsoft Access's SQL syntax does not allow for "table-less" UNION queries:

Table-less UNION query in MS Access (Jet/ACE)

This works as expected: SELECT “Mike” AS FName This fails with the error “Query input must contain at least one table or query”: SELECT “Mike” AS FNameUNION ALLSELECT “John” AS FName Is this ...

Stack Overflowmwolfe02

For example, the following works in SQL Server but not in Access:

SELECT 'Michael' As FName, 'Scott' AS LName
UNION
SELECT 'Dwight' AS FName, 'Schrutte' AS LName

The workaround is to use a single-row dummy table, as described here:

Enforcing a Single-Row Table in MS Access and SQL Server

There are many uses for a table with one--and only one--row in it. Using such a table is simpler if you can rely on the sanctity of its one-row-ness.