Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Union Queries

A Union query enables you to combine data from two tables with similar structures; data from each table is included in the output. For example, suppose you have a tblTimeCards table containing active time cards, and a tblTimeCardsArchive table containing archived time cards. The problem occurs when you want to build a report that combines data from both tables. To do this, you must build a Union query as the record source for the report. The syntax for a Union query is

Select-statement1 UNION [ALL]
Select-statement2 [UNION [ALL]
SelectStatement3] [...]

Here's an example:

SELECT FirstName, LastName, Department, Salary
FROM tblEmployees
UNION ALL SELECT FirstName, LastName, Department, Salary
FROM tblSummerEmployees

This example combines data from the tblEmployees table with data from the tblSummerEmployees table, preserving duplicate rows, if there are any.

The ALL Keyword

Notice the keyword ALL in the previous SQL statement. By default, Access eliminates all duplicate records from the query result. This means that if an employee is found in both the tblEmployees and tblSummerEmployees tables, he appears only once in the query result. Including the keyword ALL causes any duplicate rows to display.

Sorting the Query Results

When sorting the results of a Union query, the ORDER BY clause must be included at the end of the SQL statement. Here's an example:

SELECT FirstName, LastName, Department, Salary
FROM tblEmployees
UNION ALL SELECT FirstName, LastName, Department, Salary
FROM tblSummerEmployees
ORDER BY Salary

This example combines data from the tblEmployees table with data from the tblSummerEmployees table, preserving duplicate rows, if there are any. It orders the results by the Salary field (combining the data from both tables).

If the column names that you are sorting by differ in the tables included in the Union query, you must use the column name from the first table.

Using the Graphical QBE to Create a Union Query

You can use the graphical QBE to create a Union query. The process is as follows:

CAUTION

If you build a query and then designate the query as an SQL Specific query, you lose

everything that you did prior to the switch. There is

no warning, and Undo is not available!

Important Notes about Union Queries

It is important to note that the result of a Union query is not updateable. Furthermore, the fields in each SELECT statement are matched only by position. This means that you can get strange results by accidentally listing the FirstName field followed by the LastName field in the first SELECT statement, and the LastName field followed by the FirstName field in the second SELECT statement. Each SELECT statement included in a Union query must contain the same number of columns.