How to Define a Many-to-Many Relationship in Access 2003

In Microsoft Access 2003, a many-to-many relationship is an association between two tables in which a record in either table can relate to many records in the other table. To make that relationship, you add a third table and add the primary key fields from the other two tables to this table. Here are some steps on how to define a many-to-many relationship in Access 2003.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• Computer
• Access 2003

1. Close any tables you have open. You will not be able to create or modify relationships between any open tables. Press “F11” to switch to the “Database” window.

2. Click “Relationships” on the toolbar. If you have not yet defined any relationships in your database, the "Show Table" dialog box is automatically displayed.

3. Double-click the names of the tables you want to relate, and then close the “Show Table” dialog box. To create a relationship between a table and itself, add that table twice.

4. Drag the field that you want to relate from one table to the related field in the other table. Check the field names displayed in the two columns in the “Edit Relationships” dialog box to ensure they are correct. You can change them if necessary.

5. Set the relationship options if necessary. Click the “Create” button to create the relationship.

Tips & Warnings

• To drag multiple fields, press the “Ctrl” key, click each field and then drag them.
• When you close the “Relationships” window, MS Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.
• The “Query Designer” will not check to make sure a value fits within the length of the column you are updating. If you provide a value that is too long, it might be cut off.
• You cannot undo the action of executing an update query. As a precaution, back up your data before executing the query.