In a “many to many” relationship, you can record data in table, that can relate to many records in the other table. The following are the simple steps that are to be followed to establish a many to many relationship in Microsoft access. Microsoft Office Access, earlier known as Microsoft Access, is a pseudo-relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.
Whenever you want create a relationship between tables, the related fields don’t have to have the same names. However, the only thing that is to keep in mind is that the related fields must have the same data type unless the primary key field is an AutoNumber field. You can also match an AutoNumber field with a Number field only if the FieldSize property of both of the matching fields is the same. For instance, you can match an AutoNumber field and a Number field if the FieldSize property if both fields is Long Integer. Even when both matching fields are Number fields, they must have the same FieldSize property setting.
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You can create such a relationship by defining a third table, which is known as a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Writers table and the Articles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the ArticlesWriters table. The primary key of the ArticlesWriters table is the combination of the wr_id column (the Writers table’s primary key) and the Ar_id column (the Articles table’s primary key).
You can follow these steps to define a many-to-many relationship in Microsoft Access:
In the very first step you need to create the two tables, table A and table B that will be used in the many-to-many relationships. After this create another table which is known as junction table.
In the junction table all the fields are to be added with the same definitions as the Primary Key fields from the other two tables that is Table A and Table B.
While you are still in the junction table, edit the primary key to include the primary key fields from the other two tables A and B.
Now you have to create a one to one relationship between each of the two primary tables and the junction table.
After the creation of one to one relationship, just add the data to the tables using either a query that works with more than one table.
This many to many relationship are helpful in calculations as it merges two tables and then does the mathematical calculations according to your needs.