Create a many-to-many relationship

The tables in a many-to-many relationship

You have a many-to-many relationship when a single record in one table can relate to many records in another, and a single record in that second table can also relate to many records in the first. For example, say your company has several types of computers and several technicians, with each technician certified to work on some, but not all, of the computers. Each technician can be related to more than one computer, and in turn, each computer can be related to more than one technician.

To track who can work on a given machine, you create a many-to-many relationship by adding the primary keys from both sides of the relationship to a third table, called a junction or link table. In other words, a many-to-many relationship is really just a pair of one-to-many relationships.

The picture shows a typical many-to-many relationship. You can see it has a relatively simple structure — a junction table with a pair of foreign keys and some related fields.

You use the data in that junction table to answer questions such as, "Which computers did a given technician work on last month?"

You'll create a many-to-many relationship in the practice, and links in the Quick Reference Card take you to more information about this type of relationship.

Topic 8 of 10

Previous | Next

(Back to Start)

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×