databasedev.co.uk - database solutions and downloads for microsoft access

Union Queries in Microsoft Access

Creating Union Queries:

Microsoft Access allows you to create various query types using the QBE (Query By Example) pane. There are also various other queries that you can create in Microsoft Access, however you will need to create these by typing in the appropriate SQL (Structured Query Language) directly into the SQL view window when in query design.

The SQL specific queries available in Microsoft Access are:

  • Union Queries - This combines the fields from more than one table (or query) into one recordset.
  • Pass-Through Queries - Allows you to send SQL commands directly to ODBC (Open Database Connectivity) databases using SQL syntax.
  • Data Definition Queries - Allows you to create or alter database tables or create indexes in a database.

If we look at the Union Query, we will see how this lets us quickly combine fields from more than one table or query. You will see that the resultant snapshot of data is not updatable.

To perform a Union query, you must ensure that each SQL SELECT statement contains exactly the same number of fields, in the same order.

As an example, you might want to create a single set of contact details from your Customers table and your Suppliers table to send out a newsletter or Christmas card. You could create a union query to combine the data from both tables. If our tables look like the following examples:

tblCustomers
CustomerName CustomerAddress CustomerTown CustomerPostcode
Elaine Jones 1 Old Road Manchester M27 1SN
David Thomas 245 Lane End Lincoln LN34 2TH
Simon Lee 9 Swallow Lane Wigan WN3 0NR

tblSuppliers
SupplierName SupplierAddress SupplierTown SupplierPostcode
Office Suppliers Ltd 1002 Trinity Road Sth Swindon SN2 1JH
SM Electricals 3 North End Road Nottingham NG8 2LJ

We use the following SQL to create the union query:

SELECT tblCustomers.CustomerName, tblCustomers.CustomerAddress,
tblCustomers.CustomerTown, tblCustomers.CustomerPostcode
FROM tblCustomers
UNION SELECT tblSuppliers.SupplierName, tblSuppliers.SupplierAddress,
tblSuppliers.SupplierTown, tblSuppliers.SupplierPostcode
FROM tblSuppliers;

This will create the following recordset:

Showing the results of the Union Query
Showing the results of the Union Query

When you use Union command in the SQL SELECT statement, it will show only records that are NOT duplicates when it joins the multiple tables together. If you want to see all records regardless of whether there are any duplicates, you will need to use the keyword ALL after the UNION command.

This syntax required for this would be:

UNION ALL SELECT