How to Create a Union Query From Two Access Databases

Sometimes, the information you need is stored in two different Microsoft Access databases. Perhaps you want to see the complete company product inventory, but each warehouse keeps a separate inventory database. Or you need to send out invitations to the company open house, but contact information is strewn among various vendor, sales and address book databases. Luckily, Access lets you link to an external database and combine the records from multiple tables into a single result set using a union query.

Things You'll Need

• Microsoft Access 2007
• Two Access databases

Instructions

Link External Databases

1. Create a new Access database by selecting "New" from the "Office" button. Type a file name and click the "Create" button.

2. Close the empty table "Table 1" created when you created the database.

3. Open the "External Data" tab. Click the "Access" button in the "Import" group. The "Get External Data" window opens.

4. Click the "Browse" button. Select the first database containing the information you need to combine. Click the "Open" button.

5. Click the radio button next to "Link to the data source by creating a linked table." Your new database will be updated whenever the external database changes.

6. Click the "OK" button. The "Import Objects" window will open, displaying the tables in the external database.

7. Click on the name of the table containing the information you need to access. Click the "OK" button. The external table you chose is listed under the "All Tables" panel on the Navigation pane.

8. Repeat the steps to link to the table in the second database you want to use.

Create the Union Query

1. Open the "Create" tab.

2. Click the "Query Design" button in the "Other" group. The "Show Table" window opens. Click the "Close" button to close it. You will use the SQL View instead of the Design View.

3. Open the "View" menu in the "Results" group. Select "SQL View." This opens the SQL text editor in place of the Design View.

4. Replace the contents of the SQL View window with the following SQL statement. Edit the statement, changing Table 1 to the name of the first table you are using. Change column1 and column 2 to the names of the columns you want to use. Add additional columns to the list as needed, separating the column names with a comma (,):

   SELECT column1, column1
   FROM Table1

5. Add the following code on the lines after the previous statement, changing the table and column names as before to match your second table. There must be the same number of columns, and they need to be in the same order as for the first table. For example, if you are bringing in name, address and phone number columns, the corresponding columns from each table must be listed in the same order.

   UNION ALL
   SELECT column1, column2
   FROM Table2;

   The "UNION ALL" part of the statement will combine the results of both select statements, removing duplicate records. If you want to show duplicate records, remove the word "ALL."

6. Save the query, giving it a new name if desired.

7. Double-click the query in the Navigation Pane to run the union query and view the results.

Tips & Warnings

• You don't need to create a new database for your union query. You can link to an external Access database from an existing database, and create the union query between the internal and external tables.

• Additional database tables can be added to the union statement by linking to the database and adding select statements joined with UNION ALL to the query.

References

• Microsoft Office Online
• Tech on the Net

Resources

• Microsoft Office Online: Help for Access 2007