Union Query

Introduction.

Union Queries are useful for combining data from multiple tables or queries while keeping the original data physically separate.

For example, data tables from different branches (Branch 1, Branch 2, and Branch 3) are linked directly into our main system as separate files. Our task is to combine their contents to prepare monthly reports, charts, and other summaries.

One approach is to use Append Queries to merge all the data into a single table before processing. However, that creates duplication, as we’re storing the same data in two places. A better option is to use a Union Query, which combines the data virtually, without altering or duplicating the source tables. This way, branch locations can continue to update their own files independently, while our combined view remains up to date.

Unlike other queries, we cannot drag and drop tables or queries onto the Query Design grid when creating a Union Query. Instead, we must write the SQL statement manually in the SQL View. Fortunately, the syntax is straightforward—you just need a little practice to get comfortable with it.

Before we proceed, let’s look at some sample tables, including their contents, number of fields, field names, values, and data types.

Sample Tables

Table 1 Image:

Click to Enlarge

Table 2 Image:

Click to Enlarge

Creating a Union Query

Let us look at an example. The contents of the above tables can be combined in a Union Query.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.

2. In the New Query dialog box, click Design View, and then click OK.

3. Without adding tables or queries, click Close in the Show Table dialog box.

4. On the Query menu, point to SQL Specific, and then click Union.

5. Enter SQL SELECT statements to combine data from tables or queries.

Example:

TABLE [Branch1] UNION SELECT * FROM [Branch2];

OR

SELECT * FROM [Branchl] UNION SELECT * FROM [Branch2];

Copy and paste one of the above SQL strings into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in the normal datasheet view.

The output of the above query is given below:

Click to Enlarge

What Does the Union Query Do?

The above SQL statement combines data from both the Branch1 and Branch2 tables, sorting the results by the first field. Any duplicate records found in the combined data will be removed from the output.

If you have more than two tables or queries, you can extend the statement by repeating the part:

sql
UNION SELECT * FROM [xxxxxxxx]

Here, [xxxxxxxx] represents the name of the additional table or query. Continue adding these parts for each source, and place a semicolon (;) at the very end of the statement.

In simple terms, the SQL statement can be interpreted as follows:

Take all field values from all records in the Branch1 table, then take all field values from all records in the Branch2 table. Combine the results, remove any duplicates, and sort the records in ascending order based on the values in the first column.

Note that you cannot use fields with the Memo, OLE, or Hyperlink data types in the source tables or queries for this type of Union Query.

However, if you insert the keyword ALL immediately after the word UNION, the behavior changes:

• All field types (including Memo, OLE, and Hyperlink) are allowed.

• No sorting is performed.

• Duplicate records are retained.

• The records are returned in the order in which the source tables or queries are listed in your SQL statement.

An Experiment.

Let’s try another experiment. Instead of joining a different table, we will use the contents of the same table twice and observe the result. Modify the SQL string as follows, or create a new query with this statement:

sql
SELECT * FROM Branch1  
UNION  
SELECT * FROM Branch1;

Open the query in Datasheet View. What do you see? The second set of records is gone. That’s because a Union Query automatically removes duplicate records from its output unless told otherwise.

Now, modify the SQL string by adding our magic word ALL immediately after the word UNION:

sql
SELECT * FROM Branch1  
UNION ALL  
SELECT * FROM Branch1;

Open the query again in Datasheet View. This time, the table contents will appear twice in the output because duplicates are no longer suppressed.

Points to Note.,

A Union Query has certain rules that cannot be bypassed. For example:

• All source tables or queries must have the same number of fields.

• Any sorting must be done using field names from the first table or query in the statement.

These requirements are built into how Union Queries work—there is no magic word or keyword that can override them.

If the fields in your source tables or queries are arranged in a different order, or if the data types in corresponding positions differ (for example, Number in one table and Text in the other), the Union Query will still return the results in the order you specified. However, the column headings will always be taken from the first table or query in the statement. If the field order is incorrect in any of the sources, you may not notice it immediately—the mismatch often becomes apparent only when you carefully review the output. 

If you look at the sample output above, you’ll notice that the Age and Country values from Branch2 are appearing under the Salary and Address columns.

To see just how easily this kind of mismatch can happen, let’s make a small change to the structure of the second table (Branch2) and observe the output again. This will help us understand what can go wrong when we’re not careful in handling a Union Query.

1. Open Table Branch2 in the design view.
2. Click and drag the field Age and place it after the field FirstName.
3. Click and drag the Country field and place it next to the field Age.
4. Save the Table Structure.
5. Open the Union Query and inspect the output.

The shifted field values will appear exactly in their new positions, moving other values forward from the point where we removed the Age and Country fields to the left. This happens without considering the data types of the other fields in the Union Query, and no warning messages will be displayed.

In our earlier example, we selected all the fields from the member tables by using the * symbol after the SELECT clause. If you only need specific fields from the member tables or queries, you must list them explicitly and in the correct order.

For example, to select EmployeeID, FirstName, and BirthDate from both tables and display the results sorted by 'FirstName', We can write the SQL string as shown below:

SELECT [EmployeelD],
 [FirstName],
 [BirthDate]FROM [Branch1]
UNION ALL SELECT [ID],
 [FirstName],
 [BirthDate] 
FROM [Branch2]
ORDER BY [FirstName];

The 'ORDER BY' Clause can reference only the field names from the source object used in the first SELECT statement; otherwise, an error will occur when you attempt to view the results.

If you open Table Branch2 in Datasheet View, you can select and drag the Age and Address columns to any position for viewing purposes. If you save the layout, after making these changes, the columns will always appear in their rearranged positions in Datasheet View, regardless of their actual physical order in the table structure or query design.

Important: Never rely on the Datasheet View of a table or query to determine the field layout for a Union Query. Always refer to the actual table structure or the Select Query Design View to confirm the field order before adding them to the Union Query, and ensure that they appear in the same order in all member queries.

CAUTION.

When you are designing Union Queries having hundreds or thousands of records in the output, always make a point to check through the object-level record set, at least once, to ensure that they are in the correct order. Once we are sure that the output is listed correctly, then sort, suppress duplicates, or apply conditions to filter the data.

When applying conditions, you must specify Criteria at each Table or Query level.

For example, if you want to select employees born on 31st December 1960 or earlier from both tables, then you will write the SQL in the following manner:

SELECT * from [Branch1]
WHERE ([BirthDate] <=#12/31/1960#)
UNION ALL SELECT * from [Branch2] 
WHERE ([BirthDate] <=#12/31/1960#);

This will take records from each member table or query that satisfies the criteria. Remove the WHERE clause from any one position and check the output.

• Find or Filter Data on Form
• Who is Online
• Automated Email Alerts/
• Configure Outlook for Lotus Notes
• MS-Access and Email