Many to Many Relationships
Many to many relationships mean that for each record in one
table many linked records can exist in another table and vice versa. Doesn't
make sense, or don't see why you would use it. Well imagine you are keeping
track of teachers and the students in their classes at a college, where students
have many teachers. For each student there is many teachers and for each teacher
there is many students. This is a many to many relationship. This type of
relationship cannot be achieved just by linking the two tables. You need to use a joining table.
This example shows you how.
| Table | Primary Key | Other Fields |
|---|
| Tbl_Students | StudentID(UniqueKey) | FirstName, LastName, Address e.t.c |
| Tbl_Teachers | TeacherID(UniqueKey) | Firstname , LastName, Address e.t.c |
| Tbl_Relations | RelationID(UniqueKey) | StudentID,TeacherID,ClassNumber,e.t.c |
Note : We could have used StudentID and TeacherID combined as the primary key for the relations table however this would not have allowed us to add multiple classes the student may attend that that teacher holds.
In the relationships window you now can link Tbl_Students.StudentID to Tbl_Relations.StudentID (it should create a one to many)
and link Tbl_Teachers,TeachersID to Tbl_Relations.TeacherID
This is your one to many realtionship and now you could either
create a form based on teachers and have a subform based on relations, or a form
based on students and a subform based on relations. You will be able to create
new records in the sub form and access will be able to work out the link
criteria.
Advanced tip. If you want to be a little flash then when
creating the relations table make the StudentID and TeacherID lookup fields
storing the unique keys but displaying there name instead. That way in the
subform you will not need to define the combobox lookup.
| 