How to Define the Correct Join Type Between Microsoft Access Tables

By defining the join type between Microsoft Access tables, you can stipulate which records a query uses for its action. The join line shows Access how tables are related. These instructions are for Access 97.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• Microsoft Access

1. Open the Database window and click on the Query tab.

2. Click on the Relationships button on the toolbar or select Relationships from the Tools menu. The Relationships window appears.

3. If the tables you want to join are not shown, click on the Show Tables button and select them.

4. Select the field that you want to join in one table and then drag it to the field it is to join in another table. Another Relationships window appears.

5. Click on Join Type.

6. Click to put a dot beside the join type that you want to create. Click OK.

7. Click Create.

Tips & Warnings

• An inner join (the default) selects only equal fields from both tables. For example, if you are a potter, you might have a table containing records of your pots and a second table containing records of the galleries that sometimes show your work. An inner join generates data showing the pots that are currently displayed in various galleries.
• You would use a left outer join if you wanted to generate data showing a complete listing of your pots whether or not they are on display in a gallery, but you want to include gallery information beside the pots that are currently being displayed. A left outer join has an arrow pointing from left to right.
• You would use a right outer join to generate data showing all of the galleries in your database, whether or not they are currently showing your pots, but you want to include information about any pots displayed in a gallery. A right outer join has an arrow pointing from right to left.