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 the two groups. 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

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.