In this tutorial, we will teach you how to create a Many-to-Many Relationship in Microsoft Access.
Relationships in Microsoft Access refer to different tables that are linked to one another. A primary field of one table might be the foreign key of another. One such relationship is the many-to-many relationship.
A many-to-many relationship describes a scenario when a single record in a certain table can relate to numerous records in another table. And a single record in the second table can relate to many records in the first one.
Step 1 – Click on relationships
For the purpose of this tutorial, we will be using a pre-existing database. Once you have opened the database, simply go to the database tools tab and click on the relationships button. This will open up the relationships in Microsoft Access database.
Step 2 – Show table option
Next, click on the show table button available under the design tab.
Step 3 – Add tables
As a result, a new dialog box will appear on your screen. Over there, add the tables that you want to establish a relationship between.
Step 4 – Establish a many-to-many relationship
For the purpose of this tutorial, we will add the orders and products table. We will also add the junction table. You can establish a many to many relationship by adding the primary keys from both the other tables to a third a table, called a junction table. Simply drag and drop the field common to both the junction table and the order table. Check the “enforce referential integrity” option along with the cascade options right below. Once you are done, click on the create button to establish the one to many relationship. Do the same with the product table and the junction table.
Step 5 – Re-arrange the tables
Once you are done, re-arrange the tables in the relationship board such that the junction table comes in the middle. You will notice that the many-to-many relationship is simply a pair of one-to-many relationships.
In this manner, you can create a many-to-many relationship in Microsoft Access.
Home