Microsoft Access UNION and UNION ALL

This Access tutorial explains how to use UNION and UNION ALL in Access Query.

You may also want to read:

Excel VBA Union Method

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

Use UNION and UNION ALL in Access Query 01

Create a new Query:

Navigate to CREATE tab > Query Design > SQL View

Use UNION and UNION ALL in Access Query 02

 

Type the SQL as below

Use UNION and UNION ALL in Access Query 03

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

Use UNION and UNION ALL in Access Query 04

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 Query 04

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 and UNION ALL in Access Query 05

Use UNION ALL to combine the two lists

SELECT * FROM stafflist_HK
UNION ALL
SELECT * FROM stafflist_US

 

The result shows duplicated records

Use UNION and UNION ALL in Access Query 06

 

Now if we use UNION

SELECT * FROM stafflist_HK
UNION
SELECT * FROM stafflist_US

The 2nd duplicated record is removed

Use UNION and UNION ALL in Access Query 07

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

Use UNION and UNION ALL in Access Query 08

SELECT * FROM stafflist_HK
UNION
SELECT * FROM stafflist_HK

 

Duplicates removed.

Use UNION and UNION ALL in Access Query 09

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])

Outbound References

http://www.w3schools.com/sql/sql_union.asp

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *