fbpx
Logo
Mail us
ben@accessdatabasetutorial.com
accessdatabasetutorial
Home » Database Theory » Microsoft Access Tutorial: Creating A Many-To-Many Relationship

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 Tutorial 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.

Microsoft Access Tutorial

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.

Microsoft Access Tutorial

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.

Tags: , , , ,

2 Responses so far.

  1. Osa Hady says:

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