Using Union Queries / Select Statements In SQL and Access

What are they?

Normally when bringing two sets of data together in a relational database the intention is for data in the two sources to be matched in some way, for example: Customers to Invoices, Suppliers to Parts, Staff to Absence.

Occasionally, but more often than you may think, there can be a requirement to “combine” rather than “join” sets of data, for example to combine both Customers and Suppliers into one mailing list to announce significant change to, or a move for, the business.

When relating sets of data in the standard way the two sets of data must have a piece of information, a field, in common. In the case of Customers to Invoices it would be the Customer’s Account Number and then this common fact/field is used to “pair” or “relate/join” records from the two tables (Fig1 below).

In a Union Query/Select Statement there does not need to be this relating data/field.

tblCustomerstblInvoices
Account NumberAccount Number:
Invoice1
Invoice2
Invoice3….

In this relationship the number of resulting rows/records will not exceed the total number of records in the original Invoices table.

A Union Query/Select Statement will essentially place the contents of the two sets of data “on top” of one another listing for example the names and emails of both a Customer set and a Supplier set of data together into one long list.

In this relationship the number of resulting rows/records will normally be the total number of records from both tables added together.

In this relationship the number of resulting rows/records will normally be the total number of records from both tables added together.
tblCustomers
Customer 1: Name, Address
Customer 2: Name, Address
tblSuppliers
Supplier 1: Name Address
Supplier 2: Name Address

What are their requirements and restrictions?

In order to create Union Queries/Select Statements the two sources of data being combined must have structural similarities: same number of selected fields in the same order and with similar data types.

In the scenario given here both the Customer and Supplier table will have a Name, Address and Contact fields in common and it is those fields we wish to combine in order to run a mailmerge.

The two sets of data must be combined using the same number of fields in the same order but not necessarily of the same name and with similar data types. If for example the Customer table has more address lines than Suppliers, then a dummy address line will have to be created for the Supplier table.

Alternatively, if one table has a single Contact field and the other First and Lastname, then the fields will have to be combined/concatenated to create a single equivalent to Contact.

An important feature of Union Queries/Select Statements is that duplicated records are automatically excluded from the final list of records. For example if a Customer is also a Supplier they will only appear once in the final Union Query/Select Statement. If the duplicates are required for any reason then the UNION ALL operator can be used instead of the pure UNION.

How to create a Union Query in Access

To create a Union Query in Access is quite a simple task as it can be achieved by creating two comparable standard Select Queries and then combine the SQL from the two queries into one.

Prepare a Customer query to include the necessary fields for the mailmerge and with any Sorting or Filtering you require.

Note that Sorting can only be applied to the fields contained in the first query but as the fields in the second query have to be in the same order the final result of this Union will be all Companies in alphabetical order.

SQL Union Queries - 1 - Query Dialogue

 

SQL Union Queries - 2 - Query Criteria Dialogue Box

 

Leaving this query open create a second query for Suppliers.

Notice in the example below, because the tblCustomer has only one Contact field but tblSuppliers has two, a formula has been used to concatenate the First and Last Name fields together to match the single Contact field in tblCustomers. This is because the two sources must contain the same number of fields.

Also note that the field names do not have to match as in the first query for Customers two address lines are Address1 and Address2. The corresponding lines in Suppliers are Add1 and Add2.

SQL Union Queries - 3 - Editing Query Dialogue In MS Access

 

SQL Union Queries - 4 - Editing Query Criteria Dialogue Box In Access

 

Switch this second query into SQL View using the View buttons in the bottom right hand corner of the screen

SQL Union Queries - 5 - Keytip Screen Grab

 

to display the following which needs to be highlighted in full and copied.

 

SQL Union Queries - 6 - Dialogue At Bottom Of Window

Return to Query1 the Customers and again switch to SQL View. Click at the end of the SQL displayed and press Enter to create a new line.

At the start of the new line type the word UNION followed by a space and paste the contents of Query2 the Suppliers to arrive at the following:

SQL Union Queries - 7 - SQL For Query

 

SELECT tblCustomers.Company, tblCustomers.Contact, tblCustomers.Address1, tblCustomers.Address2, tblCustomers.Town, tblCustomers.County, tblCustomers.PostCode, tblCustomers.EMail
FROM tblCustomers
ORDER BY tblCustomers.Company;

UNION SELECT tblSuppliers.Company, [Firstname] & ” ” & [LastName] AS Contact, tblSuppliers.Add1, tblSuppliers.Add2, tblSuppliers.Town, tblSuppliers.County, tblSuppliers.PostCode, tblSuppliers.EMail
FROM tblSuppliers;

View the full Query in Datasheet View and 5 records will be displayed as one Customer is also a Supplier in the example given and duplicates are automatically excluded. Should you require the duplicated Customer and Supplier to be shown separately add the word ALL between UNION SELECT: UNION ALL SELECT tblSuppliers.Company, [Firstname] & ” ” & [LastName] AS Contact …..

The query will also display the five records in alphabetical order by Company/Supplier name.

SQL Union Queries - 8 - Output Of Query In MS Access

 

In Access the resulting query will be displayed with an icon of two overlapping circles to distinguish it from standard Select or Action queries.

SQL Union Queries - 9 - MS Access Icon

See here for details of our Microsoft Access training courses.

 

How To Create A Union Query In SQL

Union Queries/Select Statements can be created in any product that supports SQL. The requirements (What are their requirements and restrictions) remain the same in all instances and some environments require that the command words be in UPPER CASE exactly as below.

SELECT tblTable1.Field1, tblTable1.Field2, tblTable1.Field3, tblTable1.Field4, tblTable1.Field5
FROM tblTable1
ORDER BY tblTable1.Field1;

UNION SELECT tblTable2.Field1, tblTable2.Field2, tblTable2.Field3, tblTable2.Field4, tblTable2.Field5
FROM tblTable2;

Or to display all records from both tables regardless of any duplication of data:

SELECT tblTable1.Field1, tblTable1.Field2, tblTable1.Field3, tblTable1.Field4, tblTable1.Field5
FROM tblTable1
ORDER BY tblTable1.Field1;

UNION ALL SELECT tblTable2.Field1, tblTable2.Field2, tblTable2.Field3, tblTable2.Field4, tblTable2.Field5
FROM tblTable2;

See here for more details of our SQL training courses.

Share on LinkedInShare on FacebookTweet about this on TwitterShare on Google+Email this to someone