How to Make a New Table From Two Tables in Access


This How to is split into two parts.

To create a mail merge with MS Word, we need to have a data source that contains all the customer names, addresses, etc, that are pertinent to the labels we are going to make. The data source in this example will come from a MS Access database.

How to create a data source from two Access tables

In the Access Database are objects called Tables, Queries, Forms, etc. In this example will be only concerned with Table and Queries. The below image of the database shows you all the Tables in the database. The two tables we will be using are tbl_Customer and tbl_Address. We need to use both of these tables so that we will have all the required information to create the mail merge.

Creating a new table for the mail merge
The two tables each contain information about the customer but tbl_Address doesn't contain the first name and last name of the customer so we need to join them together.

1) Click on Queries. The New Query Window opens.

2) Select Design View.

3) Click OK.

The Show Table Window appears and allows us to select the tables we want to use in the Query we are going to make. The Query1 Window is blank at first but we will add the tbl_Address and tbl_Customer to it.

4) Select tbl_Address from the Show Table Window.

5) Click Add. The table now appears in the Query Window.

5a) Select tbl_Customer from the Show Table Window.

5b) Click Add.

6) Click Close.

The Query Window now has the two tables in it and is ready for the individual fields to be moved into the design grid.

6) Select a field in table.

7) Right click on the selected field and while holding down the mouse button, drag the field to an empty grid. Do this for all the fields you will need for both tables.

Now that you have all the fields you need for the mail merge, you need to make a new table that will combine the data.

NOTE FOR ANYONE USING CORNERSTONE'S WINDOW CLEANER DATABASE:
You need to add the Billing and Status field from tbl_Address and a -1 in the billing Criteria and "Active" (with the quotation marks) to the Status criteria. If you don't add this, you will add to the table all addresses that aren't Billing and add all moved or dead filed clients. This example uses the first name and last name which are used for residential clients.

 

To create a mail merge for your companies, follow this procedure again except replace FirstName and LastName with Contact and Company. You will still need to add the -1 to the criteria of the billing and "Active" to the status criteria.

 

The Make Table Window wants to know the name of the new table.

10) Enter the name of new table. Leave the Current Database selected.

11) Click OK

The final step is to run the Query which will make the table we will be using in the mail merge.

12) Click on the big red exclamation point.

13)Click Yes.

That's it!

The new table you made will be found in your database.

Questions or Comments? Contact Us