Page 7 of 10PREVNEXT

Build relationships for a new Access 2007 database

The tables in a many-to-many relationship

The key: Keys in a junction table

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. The picture shows a typical junction table, and you can see that its structure is relatively simple — a pair of primary key fields and some related information.

Now your data can answer questions such as, "Which computers did a given technician work on last month?" For more information about this type of relationship, see the links in the Quick Reference Card at the end of this course.

Page 7 of 10PREVNEXT