Using Union Queries

3 4

A union query combines records from two different tables or queries of identical structure. The only way to create a union query is via the SQL window. Even so, UNION isn't really a SQL statement or clause. It's a special operator used only in SQL statements to combine two recordsets into a single read-only recordset.

The basic syntax for UNION is as follows, where select1 and select2 represent two compatible queries. (Compatible in this case means that the set of fields returned by the component queries have the same structure.)

 select1 UNION  select2; 

The field order for each SELECT statement must be the same because Access matches the fields by their order, not by name.

note

---

OLE object fields aren't allowed in a union query because they contain objects (such as Microsoft Excel worksheets or Microsoft Word documents) rather than data.

Union queries aren't common, but occasionally the need for one does arise. For example, suppose you want to send the same letter to all your customers and employees. If your database is like most, you store employee and customer addresses in different tables. You can use a union query to combine all these addresses.

To use a union query, follow these steps:

1. Click Queries on the Objects list in the Database window, click New on the Database Window toolbar, make sure Design View is selected, and then click OK in the New Query dialog box.
2. Close the Show Table dialog box.
3. Select SQL View from the View button's drop-down list.
4. Enter the appropriate UNION statement, remembering to specify the same fields in the same order for both SELECT statements.

   In this example, this step creates a problem, because there's a discrepancy between the name fields. Specifically, the Employees table has two fields, LastName and FirstName, but the Customers table has only one field, CompanyName. You can't use the following statement because the field names don't match:

    SELECT LastName, FirstName Address, City, Region,  PostalCode, Country  FROM Employees  UNION SELECT CompanyName, Address, City, Region,  PostalCode, Country  FROM Customers;  

   Fortunately, concatenating the LastName and FirstName fields, as shown in Figure 11-21, solves the problem.

   

   Figure 11-21. The union query in which the field names don't match.

   The proper statement lists the same fields: MailingName, Address, City, Region, PostalCost, and Country:

    SELECT FirstName & " " & LastName As MailingName, Address, City, Region, PostalCode, Country  FROM Employees  UNION SELECT CompanyName As MailingName, Address, City, Region, PostalCode, Country  FROM Customers;  

   The Employees table's MailingName field is a combination of the LastName and FirstName fields and the Customers table's MailingName field comes from the CompanyName field, renamed MailingName. The renaming isn't necessary; it's been added for readability. As long as the number of columns in each SELECT is the same and the corresponding columns are the same data type, Access doesn't really care about each column's name. When column names differ, the query uses the name from the first SELECT statement.

5. Run the query to see the results shown in Figure 11-22.

   

   Figure 11-22. The previous union query returns a combined recordset of names and addresses from the Customers and Employees tables.

One common use for union queries is to add a None or an All option to a combo box's list so that users can return to no selection after making a selection or select to use all the records in an operation (say, selecting records for a report). The combo box or list box with one of these selections should be unbound; otherwise, you could save an inappropriate value in a field.

The union query listed below creates All and None options for a combo box or list box's RowSource property. You could display either the CustomerID column, with a blank for the None selection and an asterisk (*) for the All selection, or the text (All) and (None) for the CompanyName field, depending on your preference. (Both columns are displayed in the sample list box shown in Figure 11-23.) Parentheses are used to force these special entries to the top of the list.

Here's the SELECT statement we'll use:

 SELECT "" As CustomerID, "(None)" As CompanyName FROM Customers UNION SELECT "*" As CustomerID, "(All)" As CompanyName FROM Customers UNION SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID; 

A list box with this union query as its RowSource is shown in Figure 11-23.

Figure 11-23. This list box displays customers with a (None) selection and an (All) selection created by a union query.

Clicking the Show Selected Customers command button opens (or requeries) a datasheet form based on a query limited to the selection in the list box.

InsideOut

By default, UNION omits duplicate records. To display duplicate records, add the ALL option in the following form:

 select1 UNION ALL select2;  

If you know there are no duplicates and you're working with a large number of records, adding the ALL option can speed up the query a bit because Access is able to skip the comparison step.

Working with Dissimilar Column Types

In Access, it doesn't matter if the field names don't match, but it does matter if the data types aren't compatible. When combining data from two fields in an expression, Access converts the resulting column to a data type that's compatible with all its components. For example, if you combine a Long Integer and an Integer field into a single query field, the resulting column will be a Long Integer. If you combine text and numbers, Access returns a Text column, although it's hard to imagine why you'd want to do this. Think long and hard before you turn a number field into text.

Sorting the Results

As with most SQL recordsets, you can add an ORDER BY clause to a UNION query in the following form, where field identifies the field by which you want to sort the resulting recordset:

 select1 UNION ALL select2 ORDER BY field; 

For instance, to sort the example recordset by Country, use the following statement:

 SELECT FirstName & " " & LastName As MailingName, Address, City,   Region, PostalCode, Country FROM Employees UNION SELECT CompanyName As MailingName, Address, City, Region,   PostalCode, Country FROM Customers ORDER BY Country;