UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Junction Tables (Many-to-Many Relationships)    
Junction Tables (Many-to-Many Relationships)

When you need to establish a many-to-many relationship between two groups, the simplest solution is to use a Junction Table. A Junction Table (sometimes referred to as a "Bridge Table") is a table that contains references to both groups; bridging them together. For example: A school includes two groups: Students and Classes. One student can attend many classes, and one class can contain many students. Hence, the "many-to-many" relationship between students and classes. Here's how you relate the two groups using a Junction Table:

tblStudents
StudentID [Primary Key, AutoNumber]
strFirstName
strLastName

tblClasses
ClassID [Primary Key, AutoNumber]
strClassDescription

tblClassMembers
ClassMemberID [Primary Key, AutoNumber]
lngClassID [Foreign Key to tblClasses.ClassID]
lngStudentID [Foreign Key to tblStudents.StudentID]
strSeatLocation

Image:JunctionTableExample1.jpg

The Junction Table, tblClassMembers, contains one record for each student/class combination. Note that tblClassMembers can contain additional information about that combined piece of information: strSeatLocation. Because you know the student and the class, you can describe where the student sits during that class.

Because AutoNumber fields are the most common Primary Key for a table, you will just need to use a Long Integer field in your Junction Table to provide the link, since Autonumber fields are, by definition, Long Integer data type.

Data Entry

When deciding how to set up your data entry form, you've got three basic options:

1. Create a form based on the tblClasses table, and a continuous subform based on the tblClassMembers table. In the main form, browse to the class (or create a new record for a new class). In the subform, use a combobox to select each student that is attending the class.

2. Create a form based on the tblStudents table, and a continuous subform based on the tblClassMembers table. In the main form, browse to the student (or create a new record for a new student). In the subform, use a combobox to select each class that this student is attending.

3. Create a form based on the tblClassMembers table. Use comboboxes to select the student and the class.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 33,308 times.  This page was last modified 18:21, 13 May 2014 by doctor9. Contributions by BananaRepublic, Jack Leach and Mark Davis  Disclaimers