How to Define the Correct Join Type Between Tables in Access 2003

You can specify join types between tables or fields in Microsoft Access 2003 to expand or narrow the results of your search. For example, choosing a full join expands the results to include records that match and do not match the join condition. 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. Here are some steps to help you define the correct join type between tables in Access 2003.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• Microsoft Access 2003
• Computer

1. Open the Access 2003 program by selecting the Access icon in the Start menu on your computer.

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

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

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

5. 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.

6. Click on Join Type.

7. Click to put a dot beside the join type that you want to create and click "OK." (See tips below to understand different join types.)

8. Click "Create" to establish the join type that you want. Save your settings by clicking on the "Save" icon in your toolbar before closing the program.

Tips & Warnings

• An inner join (the default) selects only equal fields from both tables. For example, if you are a potter, you might have one 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.