Microsoft Office Tutorials and References
In Depth Information
What Are Relationships?
The problem is that Access (and most other relational database programs)
can’t handle many-to-many relationships. Access refuses to accept that
these relationships exist. (Don’t we all know people like that?) But don’t
worry — you can work around this problem. You can create an additional
table that saves the day: The new table records the connections between the
two tables.
Book I
Chapter 3
In the students-and-courses example, you can make a new table called
CourseRegistrations. This new table, called a junction table, contains one
field that matches the primary key for Courses and one field that matches
the primary key for Students; each record in the CourseRegistrations
table connects one student to one course. The Students table and the
CourseRegistrations table have a one-to-many relationship: The Students
table is the master table, and CourseRegistrations is the detail table. The
Courses table and the CourseRegistrations table also have a one-to-many
relationship: Again, the Courses table is the master table. In fact, you
probably want this new table anyway because you need some place to record
each student’s grade in that course. (We frequently find that the new
junction table is useful for storing more than just the relationship.)
Figure 3-4 shows the relationships among the three tables: Students,
CourseRegistrations, and Courses. To provide a single primary key field
that uniquely identifies each student, we added a StudentID field to the
Students table. Each record in the CourseRegistrations table connects one
student (by student ID) to one course (by class number). In real life, we’d
add fields for the student’s grade, registration date, payment date, and other
information about the student’s enrollment in the course.
Figure 3-4:
To store
a
manyto-many
relationship,
create a
junction
table that
connects
the tables.
Search JabSto ::




Custom Search