Microsoft Access Office VB VBA Help and Examples

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.

TablePrimary KeyOther Fields
Tbl_StudentsStudentID(UniqueKey)FirstName, LastName, Address e.t.c
Tbl_TeachersTeacherID(UniqueKey)Firstname , LastName, Address 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.

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.