The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

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!


©2014 Blue Moose Technology, LLC

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

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)