Many to Many Relationships in Access

A many-to-many relationship is used when a record in the first table can have many matching records in the second table or when the second table can have many matching records in the first table. For example, in an e-commerce website, a customer may have one order with a number of different products and a single product may have many orders. A many-to-many relationship cannot exist on its own. A related junction table must be created with one-to-many links to the two main tables. It has to contain two fields with the foreign keys from both tables.

1. Open a database with at least three tables of data

2. An intermediate table must be included to form a Many-to-Many relationship between two tables

3. A primary key for the first table must be created with two fields

4. Open the first table in Design View

5. Click and drag to the left of two fields

6. Apply a Primary Key to both fields

7. Save the table and close it

8. Open the Relationships window

9. Add the first table

10. Create a Many-to-Many relationship between the first table and the third table using one field. A One-to-Many relationship between the first table and the second table has been created. There has been a Many-to-Many relationship created between the first and third tables as a result of the two One-to-Many relationships.

For more information about Advanced Access click the link below: