This Access tutorial explains how to use UNION and UNION ALL in Access Query.
You may also want to read:
UNION and UNION ALL in Access
UNION and UNION ALL are used to combine two Query results vertically into one Query, therefore both Queries require to have the same number of fields and same data types.
When two Queries are combined, there could be rows of record that are exactly the same (duplicates). UNION removes all duplicates while UNION does not remove anything.
UNION and UNION ALL commands have to be used in SQL View, meaning you have to type the SQL commands directly, you don’t have a Query Design View (the friendly drag and drop user interface) to use the commands. However, you can still write the two Queries separately in Query Design View first, and then use UNION to combine them.
Use UNION and UNION All in Access – no duplicated records
Assume that we have two staff lists as below without duplicated data
Create a new Query:
Navigate to CREATE tab > Query Design > SQL View
Type the SQL as below
The syntax of UNION is
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
For my case I want to select all columns, so I use *
Save and run the Query
Now we can see the results of stafflist_HK is on top, while resultlist_US is at the bottom.
This time instead of typing UNION, type UNION ALL
SELECT * FROM stafflist_HK UNION ALL SELECT * FROM stafflist_US
View the result, you will find no difference as before, it is because UNION is used to remove duplicate records, but the data set has no duplicates.
Use UNION and UNION All in Access – with duplicated records
Add employee 001 to stafflist_US so that both tables contain the same records
Use UNION ALL to combine the two lists
SELECT * FROM stafflist_HK UNION ALL SELECT * FROM stafflist_US
The result shows duplicated records
Now if we use UNION
SELECT * FROM stafflist_HK UNION SELECT * FROM stafflist_US
The 2nd duplicated record is removed
Use UNION to remove duplicates within Table / Query
Assume that you have a Table or Query that contains duplicates. Use UNION to self join the Table
SELECT * FROM stafflist_HK UNION SELECT * FROM stafflist_HK
Duplicates removed.
Use UNION in Create Table
To insert UNION result in a new table, you may create a Query first and then use Create Table.
Alternatively, you can directly write a SQL statement to perform UNION and create table in one Query.
Using stafflist_HK and stafflist_US as an example
SELECT * INTO [new_table_name] FROM (Select * from [stafflist_HK] UNION ALL Select * from [stafflist_US])