MS Access - Many-To-Many Relationship


Advertisements


In this chapter, let us understand Many-to-Many Relationship. To represent a many-tomany relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. To do so, we also need to add a junction table. Let us first add another table tblAuthers.

TblAuthers

Let us now create a many-to-many relationship. We have more than one author working on more than one project and vice versa. As you know, we have an Author field in tblProjects so, we have created a table for it. We do not need this field any more.

TblAuthers Description

Select the Author field and press the delete button and you will see the following message.

Dialog Box

Click Yes. We will now have to create a junction table. This junction table have two foreign keys in it as shown in the following screenshot.

Foreign Keys

These foreign key fields will be the primary keys from the two tables that were linked together — tblAuthers and tblProjects.

To create a composite key in Access, select both these fields and from the table tools design tab, you can click directly on that primary key and that will mark not one but both of these fields.

Table Tools Design

The combination of these two fields is the tables’ unique identifier. Let us now save this table as tblAuthorJunction.

The last step in bringing the many-to-many relationships together is to go back to that relationships view and create those relationships by clicking on Show Table.

Select the above three highlighted tables and click on the Add button and then close this dialog box.

Highlighted Tables

Click and drag the AuthorID field from tblAuthors and place it on top of the tblAuthorJunction table AuthorID.

TblAuthers Junction

The relationship you’re creating is the one that Access will consider as a one-to-many relationship. We will also enforce referential integrity. Let us now turn on Cascade Update and click on the Create button as in the above screenshot.

Cascade

Let us now hold the ProjectID, drag and drop it right on top of ProjectID from tblAuthorJunction.

Referential Key

We will Enforce Referential Integrity and Cascade Update Related Fields.

Cascade Update

The following are the many-to-many relationships.

Many to Many

Advertisements
E-Books Store