Microsoft Access Union Queries (Part 2)

Let’s say we have two tables and you want to create a basic Union Query between them. Our two tables are tbl_CountryClubs and tbl_CountryClubsAlt. The tables have the same fields and field names, so your Union Query will look as follows:

SELECT * FROM tbl_CountryClubs
UNION SELECT * FROM tbl_CountryClubsAlt;

That’s it. After typing this into the SQL view of the query, click to view the datasheet and you’ll see the records merged together even though they come from different tables.

Figure 1

In a different situation, you may want to only show a select number of columns from your tables. You can change the SQL Statement in order to select just the ClubName and City fields:

SELECT [ClubName], [City] FROM tbl_CountryClubs
UNION SELECT [ClubName], [City] FROM tbl_CountryClubsAlt;

Figure 2

You can also order Union Queries by a certain field by including an ORDER BY statement at the bottom of the rest of your SQL. The following example would produce a result in alphabetical order by City:

SELECT [ClubName], [City] FROM tbl_CountryClubs
UNION SELECT [ClubName], [City] FROM tbl_CountryClubsAlt
ORDER BY [City]

If you need to filter the records for a particular value you can do so by adding a WHERE statement in your SQL, this would pull all of the values from the first table where City is equal to “Lancaster” and then pull the records from the second table where City is equal to “Lancaster”, then display them in the datasheet:

SELECT [ClubName], [City] FROM tbl_CountryClubs
WHERE [City] = “Lancaster”
UNION SELECT [ClubName], [City] FROM tbl_CountryClubsAlt
WHERE [City] = “Lancaster”;

Union Queries, by default, eliminate duplicate records if they appear, for instance if you used the following SQL Statement, even if you had multiple records for Lancaster, you would only see one unique combination of each:

SELECT [City], [Zip Code] FROM tbl_CountryClubs
WHERE [City] = “Lancaster”
UNION SELECT [City], [Zip Code] FROM tbl_CountryClubsAlt
WHERE [City] = “Lancaster”;

Figure 3

In order to show all records regardless of duplicates, you’ll need to change “UNION” to “UNION ALL”. Notice the change in the results:

SELECT [City], [Zip Code] FROM tbl_CountryClubs
WHERE [City] = “Lancaster”
UNION ALL SELECT [City], [Zip Code] FROM tbl_CountryClubsAlt
WHERE [City] = “Lancaster”;

Figure 4

This should give you a great jump-start in using these powerful queries for your own data. When used properly, Union Queries can save you space in your database, headaches in your data, and plenty of time when you’re creating large-scale reports or mining your data archives. Use them often!

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Microsoft Access Union Queries (Part 2)