In a typical query, two tables with different fields can be joined as long as there is a common field. The combined set of fields is linked by the common field that both tables share.

Take a look at a query we used in last month's Wizard:

In this query, the common field for the two tables is the CabinKey. The resulting query brings the fields together from both tables, linked by the common field.

A Union Query is fundamentally different. A Union Query is unique because it brings the same fields from two or more tables together. Think of your standard query as putting two tables side-by-side, linked by a common field. In a Union Query, you put the tables one on top of the other.

Take the following tables

The first table contains 7 records of active customers; the second table contains 8 records of inactive customers. These two tables contain the same fields and the same type of data in each of their fields. In this case, the names of the tables describe their function. As I mentioned earlier, this application is poorly designed. A more effective approach would be to include a field in the customer table indicating the status of each customer (active or inactive).

Now let's say that you want a report that shows all of your customers, regardless of status. Our goal is to get one query that contains the full set of data from both tables. You could, as my client did, create a third table and append each of the two tables into the new table and use that; however a Union Query is a much more efficient solution.

Building a Union Query is not as straightforward as your typical query. You must build it in the SQL design Window. For details on the SQL design window see the (June 2005 edition)

Open up a regular query window in design view (From the Query tab choose New | Design View). This will take you to the standard design window. Access will invite you to select tables for your query; choose Close to dismiss that dialog box. On the menu at the top of the screen choose View | SQL View. This is where we'll build our Union Query. The format is:

select (fields) from (source table 1 name)
union
select (fields) from (source table 2 name)

So, for this example, you'd type:

select * from tblcustactive
union
select * from tblcustinactive

(Read the details below to find out more about the use of the wildcard (*) to designate fields.)

When you run this query you get:

The result is one combined table showing all 15 of our customers combined from both the active customer table (7) and the inactive customer table (8).

Now if you attempt to look at this query in design view, you'll automatically be redirected to the SQL window. Access has no way to represent a Union Query visually.

In SQL "speak", the Union Query joins two standard select statements with a union statement. The statement format is relatively straightforward, and looks like this:

select (fields) from (source table 1 name)
union
select (fields) from (source table 2 name)

You have control over the way you select fields in each statement. Let's say that your source tables have a different number or order of fields, or that you really don't want every single field to show up in the table resulting from the Union Query. You can still use the Union Query, you just have to go into more detail.

Rather than using the wildcard (*), which selects all fields from the table, you could type, for example:

select FieldName1, FieldName2, FieldName5 from Table Name (list all of the field names you want, separated by commas)

For example, to get just the first and last names from each table in one common table, you could type:

select FirstName, LastName from tblcustactive
union
select FirstName,LastName from tblcustinactive

The goal is to specify the column(s) you want to return from the query explicitly listing each one, rather than using the select * statement.

There are two hard and fast rules you must follow when constructing a Union Query:

1. The number of fields returned by each select statement must be the same when you join them with the Union command
2. The field type for each query must be compatible, (i.e. if the first field in the first query is a date, the first field in the second query must be a date). Remember, the fields listed in each select statement will line up under a common column in the resulting table.

You can also sort the results of your Union Query by adding an "order by" statement at the end of your last select statement. You must use a field from the first Select statement in your "order by" statement (See the June 2005 Wizard for more details on the various pieces of the SQL statement)

One other point to note about Union Queries; there is an upper limit to how many select statements you can have. It's limited by the length of the SQL string. The maximum size is about 64K (it varies from Access version to Access version). 64K is a big query, and in practice it's unlikely, you'll hit it.

Union Queries are a great way to recover from poor database design or to bring together similar data from sources outside of Access. It can save the day when you want to pull together similar data from more than one data source. Just remember that the Union Query statement has some particular requirements, namely that it must contain the same number and field types from each of the source tables.

Union Queries are notoriously difficult to debug. The primary reason for this is that Access can't visually represent your Union Query as it does a standard query.

There are a couple of ways to work around this problem. The first is to build each query separately in a standard query window then paste the resulting SQL string into your union statement.

The second is to take your separate select statements, paste them into a new query window, and look at the query in the standard query design window. The results of a standard query can be evaluated with your debugging skills to figure out how to resolve any problems.