FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Linking MS Access Fields Using a Union Query

Have you ever found yourself wishing you could link different fields together? What about combining fields from different tables? Well a Union Query may be just what you are looking for.

Here is the basic syntax for a union query:

SELECT FieldOne, FieldTwo FROM TableName WHERE Condition
UNION
SELECT FieldThree, FieldFour FROM TableName WHERE Condition

If the fields have different names you can change the code slightly to:

SELECT FieldOne AS NewName1, FieldTwo AS NewName2...

Here are a few situations where a union query can help:

You have a table with the following fields:

FirstName, LastName for delegates to a conferenece and
ShareFirst and ShareLast for their guests

Let's assume you have entered the following data:

Suddenly you find that you just want a list of all the people at the event, regardless of whether they have been entered into the delegate or the guest fields.

Open a new query in Access, and then go to SQL View. Type the following into the SQL window:

SELECT FirstName, LastName from tContacts
UNION
SELECT ShareFirst, ShareLast from tContacts

This will produce the following list:

OK, now lets sort it by LastName and then FirstName. Change the code to this:

SELECT FirstName, LastName from tContacts
UNION
SELECT ShareFirst, ShareLast from tContacts
ORDER BY LastName, FirstName

This is how the Query has changed:

Now, if you want to combine their names into an alphabetical list (such as LastName, FirstName), then change your code to the following:.

SELECT LastName & ", " & FirstName as FullName from tContacts
UNION
SELECT ShareLast & ", " & ShareFirst from tContacts
ORDER BY FullName

The query now looks like the following:

This just formats the name as "Jones, Bill" and calls the combined field FullName. It also sorts it alphabetically.

Don't forget, just because in this case all of the fields were from one table, you could have just as easily combined fields from multiple tables. Just replace tContacts with the names of the tables you wish to combine the data from.