MS Access - Many-To-Many Relationship

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.

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.

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

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.

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.

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.

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

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.

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

We will Enforce Referential Integrity and Cascade Update Related Fields.

The following are the many-to-many relationships.