How to Add a One-to-Many Database Relationship in Access 2003

You can use Microsoft Access 2003 to search for and share data across multiple tables. For example, suppose you are a potter and you want to keep track of your business operations. You could create one table to keep records of your pottery designs and a second table to store records of the galleries that display your work. By creating a relationship between the two tables, you could run a query that would show where each of your items is currently being displayed. Here are some steps to add a one-to-many database relationship in Access 2003.

Things You'll Need

• Microsoft Access 2003
• Computer

Instructions

1. Open Microsoft Access 2003 on your computer by clicking the icon on your desktop or accessing it through your Start menu.

2. If you haven't already, assign a Primary Key as a unique identifier in a field in each table.

3. Close all tables and open the Database window. Click the Relationships icon on the toolbar. If your database has existing relationships defined, a visual display of these relationships will appear.

4. Click "Show Table" on the toolbar if your two tables do not appear. The Add Tables/Queries box will appear.

5. Double-click on the two tables from the Add Tables/Queries box that you want to relate.

6. Close the Add Tables/Queries dialog box.

7. This can be the tricky part. Drag the field that you want to relate from one table to the related field in the other table. This field is usually the Primary Key field in one table. The Primary Key field in the other table is called the Foreign Key.

8. When the Relationships dialog box appears, check to be sure that everything is as you want it, then click "Create." The relationship is established. You will now see your two tables displayed with a line joining them to indicate that they are related.

Tips & Warnings

• This type of relationship is called a one-to-many relationship. In the pottery example, you would have only one record of each pottery item, but each gallery could display several items.

• You can also have a one-to-one relationship and a many-to-many relationship.

• Related fields almost always need the same datatype, and the Field Size property setting must be the same.