How to Define a Many-to-Many Relationship in Microsoft Access

In a "many to many" relationship, one record in either table can relate to many records in the other table. For example, in a library database, one record in your Titles table could relate to several records in your Borrower table, since several people might have signed out copies of the same book. Conversely, each record in your Borrower table could could be related to several book titles, since each borrower might have signed out several books. These instructions are for Access 97.

Difficulty: Moderate

Instructions

Things You'll Need:

• Microsoft Access

1. Create the two tables that will be used in the many-to-many relationship.

2. Create another table, called a junction table.

3. In the junction table, add fields with the same definitions as the Primary Key fields from each of the other two tables.

4. Still in the junction table, edit the primary key to include the primary key fields from the other two tables.

5. Create a one-to-many relationship between each of the two primary tables and the junction table. (See the related eHows for instructions.)

6. Add data to the tables using either a query that works with more than one table or by creating a form that works with more than one table.

Tips & Warnings

• The primary key fields in the junction table serve as foreign keys. Foreign keys indicate how the tables are related.

[johnmichaels]

on 4/4/2008 A better example is phone number to person. You as an individual, may have several phone numbers, home, work, cell, etc. A phone number can be associated with more than one person. You may share a home number with a spouse, roommate, etc. You may have a switchboard at work where all calls go into a common number and then are directed from there. The junction table would have the primary key from the person table and the primary key from the phone number table. You should also be able to create a unique index on these two fields.

[johnmichaels]

on 4/4/2008 The article has a decent description of the use of junction tables, although I find the example of a library database to be a poor example. Typically a true junction table has only the primary key from the other two tables, other than some possible "housekeeping" data like a time stamp. The combination of the two indexes in that table are unique in the table (you could create a unique index on those two fields). In a library database, you would have a master table of books and a table of borrowers, but you would have a table similar to a sales transaction table that would have borrowing history that would include information like the date checked out, date checked in, etc.. Also, a person could check out the same book multiple times, which would result in multiple links between the person and the book. This is not a true junction table.

A better example is phone number to person. Yo