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

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

Let’s revisit a popular aspect to your Microsoft Access database design; Creating a many-to-many relationship.

Firstly, watch this quick video which explain the key components and the starting steps to getting to the many-to-many state for your Access database.

In order to create this type of relationship, it’s now clear we need an interceding or junction table to handle the two-way joins of the outer tables with the option of setting referential integrity option which tightens the operational side of when entering data.

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

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.

access many to many diagram

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.

This entry was posted in Database Theory, MS Access, Tables, Utilities and tagged , , , , . Bookmark the permalink.

2 Responses to Microsoft Access Tutorial: Creating A Many-To-Many Relationship

  1. Ben says:

    Data modelling is an art form and takes time to master. I have eBook that cover relational design techniques which may help ERD

  2. Osa Hady says:

    Nice article, if please could provide us with other articles about how to design and establish ERD.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.