Sample of many-to-many relationship

[Tim]

Can you show me where I can look at an example of many-to-many relationship?
I have a Stud table and a Teacher table. I plan to create an intermediate
table called StudTeacherCombo with a student is assigned to more than one
teacher. I do have data in both tables and now if I create the new
StudTeacher combo, how can I fill in data in this table? I guess Northwind db
or Pub database would have something about it, but where exactly it is? -
thank you for your help in advance.

 

[mscertified]

Basically you will have a continuous form with 2 combo boxes (Teacher,
Student), the first with entries in the first table and the second with
entries in the second table. You then just select the combinations. Of course
you need to somehow prohibit duplicate combinations.
There are other more sophisticated ways to do it but the above is the
simplest.

-Dorian

 

[Tim]

Do you mean I don't need the intermediate table? What is the record sources
of these combo boxes?
Thanks

 

[mscertified]

Your teacher tabel has TeacherID (primary key) and name
Your student table has StudentID (primary key) and name
Your Xref table has TeacherID and StudentID, make both the primary key so
the combination must be unique.
Combo box 1 is based in Teacher table, bound to TeacherID, displays name
Combo box 2 is based on Student table, bound to Student ID, displays name
Form is based on Xref table

-Dorian

 

[John W. Vinson]

Can you show me where I can look at an example of many-to-many relationship?
I have a Stud table and a Teacher table. I plan to create an intermediate
table called StudTeacherCombo with a student is assigned to more than one
teacher. I do have data in both tables and now if I create the new
StudTeacher combo, how can I fill in data in this table? I guess Northwind db
or Pub database would have something about it, but where exactly it is? -
thank you for your help in advance.

The Orders form in Northwind models just such a many to many relationship:
each Order includes one or more Products, and each Product can be in one or
more Orders.

In your case, you would use a Form based on either Stud or Teacher (depending
on whether you want to assign teachers to each student, or students to each
teacher; you might want both forms). On this form you would have a Subform
based on StudTeacherCombo. If your form is based on Stud, you would use the
StudentID as the master/child link field, and have a combo box on the subform
bound to the TeacherID, based on the teacher table.

John W. Vinson [MVP]

 

[Tim]

I GOT IT!!!!!

Thank you both of you, Dorian and John. I appreciate your help!!!

tim