The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
July 2005

Union Queries – The Urge to Merge

While visiting a customer recently, I noticed that she had two nearly identical Excel spreadsheets serving slightly different purposes. One of the spreadsheets contained the names and addresses of active customers; the other held the same information for inactive customers. In order to create a single report, she imported both Excel Spreadsheets into Access and then ran an append query to put the results into a single table. She used the merged table to create her monthly reports.

My first thought was about the excessive amount of time this operation must take her every month. My next thought had to do with human error; the messier the system, the more chance for somebody to screw up (not only at report-writing time, but also when entering data). Unfortunately, I see this situation where users create separate spreadsheets or tables and join them with append queries fairly often. Sometimes it's for cases where the source data is coming from someplace else, like Excel. Other times, it's as a result of imperfect table design. In this case, a better design would be to create one table for customers with a field for "status" (active or inactive, see the Wizard normalization articles for more information about data construction).

If there is no way around using separate tables that must be merged, a better approach is to create one query or report with all the combined data using of a Union Query, the focus of this month's Wizard.

In this issue
  • Tip of the Month
  • The Basics
  • The Details
  • Conclusion
  • Trap (and Work Around) of the Month

  • The Basics

    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)
    select (fields) from (source table 2 name)

    So, for this example, you'd type:

    select * from tblcustactive
    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.

    The Details

    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)
    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
    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.

    Trap (and Work Around) of the Month

    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.

    Tip of the Month

    An effective way to build complex Union Queries (i.e. Union Queries with more than two SELECT statements or those calling out specific fields) is to:

    1. Build each SELECT statement separately
    2. Copy the SQL statement from the SQL view window
    3. Paste the results into a new query
    4. Insert the Union statement between the two SQL statements

    If you do this, make sure that you delete the trailing semi-colon on all but the last SQL statement. The semi-colon tells Access the SQL statement is finished. If you leave it in, you'll get an error when you try to run the query.

    If you have a more complex situation, you can also build two (or more) separate queries with the same number and type of fields and then join them in a Union Query by typing:

    Select * from QueryName1
    Select * from QueryName2

    QueryName1 and QueryName2 represent the names you gave to the queries you built that contain the fields you want returned to your Union Query

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz


    Custom Software | - | Westford | MA | 01886