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

Use Access relationships to search for and share data across multiple tables. For example, suppose you are a potter and want to keep track of your business operations. You could create one table to keep records of your pottery 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.

Difficulty: Moderate

Instructions

Things You'll Need:

• Microsoft Access

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

2. Close down all tables, then press the F11 key to 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.

3. If your two tables do not appear, click the Show Table icon on the toolbar. The Add Tables/Queries box will appear.

4. From the Add Tables/Queries box, double-click on the two tables that you want to relate.

5. Close the Add Tables/Queries dialog box.

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

7. 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 relationshhip. 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 FieldSize property setting must be the same. There are two exceptions when different datatypes are permissible.