Microsoft Access Tutorial: Creating A Many-To-Many Relationship

Microsoft access tutorial In a relational database, a many-to-many relationship exists when a record in one table can be referenced by one or more records in another table and vice versa.

You need to have two or more tables to create relationships. To link these tables, you will have to create another table (a third) which will be your interceding or junction table.

Here are the steps again in creating a third table:

1. Go to the Create tab.

2. Click on Table Design. A new table will be opened.

3. Create two fields (plus any additional supporting fields).

NOTE: The two key fields are going to be the primary keys from the two tables that you have created which are in effect known as secondary or foreign keys (duplicates OK).

• To identify the primary key from a table:
• Click on the existing table and select View on the upper left corner of MS Access.
• You can identify the primary key when you click on one of the fields and see the Primary Key button, right next to the View button, being highlighted in orange.
• Once you find the primary key, take note of three things: Field Name, Data Type, and Field Size (located at the bottom).
• Type in the Field Name in the first field of your junction table. Indicate the Data Type and the Field Size.
• Be sure to include the primary key from each of the tables.

4. Designate all fields as the table’s primary key.

• Hover your mouse over the selection box next to the first field.
• Click, hold and drag your mouse down to the last field to select all fields.
• Click on Primary Key button on the upper left corner.

5. Save the new third table. Label it accordingly for easy identification.

Next, Creating a many-to-many relationship:

1. Go to the Database Tools tab.

2. Click on the Relationships button. You will see the tables.

3. Hover over the primary key of the first table. 

4. Left click the mouse, hold, drag and drop it over the top of its counterpart in the junction table.

5. Once you release your mouse, a dialogue box where you can edit relationships will pop up.

• Click on Enforce Referential Integrity.
• Click the Create button.

6. Repeat steps 3, 4 and 5 for the other tables.

An Access database many-to-many relationship is made of two one-to-many relationships. Look at the diagram below.

To learn more about Microsoft Access database relationships and the concepts behind RDBMS (Relation database management systems), why not take a look at my eBook on Understanding Access Database Relationships (RDBMS) – Joining Tables which comes with a guaranteed 30 day money back policy and email support.   Microsoft access tutorial

34 Edgwarebury Lane,Edgware,Middlesex,HA88LW,UK