MS Access is a many-to-many relationship

In this chapter, let's look at many-to-many relationships. T o represent a many-to-many relationship, you must create a third table, often referred to as a join table, that breaks down the many-to-many relationship into two one-to-many relationships. 30> et's start by adding another table, tblAuthers.

Now, let's create a many-to-many relationship. W e have more than one author working on more than one project and vice versa. A s you know, we have an author field in tblProjects, so we created a table for it. We don't need this field anymore.

Select the author field, and then press the delete button, and you'll see the following message.

Click Yes. W e must now create a join table. There are two foreign keys in this join table, as shown in the screenshot below.

These foreign key fields will be the primary keys in the two tables linked together - tblAuthers and tblProjects.

To create a composite key in Access, select both fields, and then click the primary key directly from the Table Tool Design tab to mark both fields between them between them.

The combination of these two fields is the unique identifier of the table. Now let's save this table as tblAuthorJunction.

The final step in putting many-to-many relationships together is to return to the relationship view and create those relationships by clicking the Show table.

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

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

The relationship you create is a relationship that Access will treat as a one-to-many relationship. W e will also implement reference integrity. Now let's open Cascade Update and click the Create button, as shown in the screenshot above.

Let's keep ProjectID now and drag and drop it to tblAuthorJunction at the top of ProjectID.

We'll force reference integrity and cascading to update related fields.

Here are the many-to-many relationships.