Many to Many Relationship

[Mike]

Hello,

I have a table with students (studentID) and parents
(parentID). some students have more than one set of
parents (divorce cases), and some parents have more than
one student (siblings). I've created a junction table
with studentID and parentID both as primary indexes.
Now...how do I use this to set up forms/subforms so I can
see everyone relating to everyone else?

Thanks.

Mike

 

[Ken Snell]

Depends upon what you want to display and what you want to be "selecting".

If you want to select a student and then see the parents for that student,
create a form that is based on the junction table, put controls on the form
in the detail section that are bound to the fields in that table, and set
the form to Continuous Forms view. Then turn on the Control Wizard, select a
combo box control, click on the form, and tell the wizard that you want to
use the combo box to look up records on the form. The wizard will give you
choices for how to set this up. Then you can use the combo box to select a
student (for example) and have the student's parents displayed on the form.

 

[Mike]

Ken,

Thanks for your reply. I already have a form that has
students as the main form, and their parents as a
subform. The link field is the studentID, so I have a
corresponding studentID in the parent file. However, in
instances where there are sibling students, I have to
create a duplicate record for the parent(s) in the
parents table so the parents can also relate to the
sibling's studentID number. What I would like is a way I
can simply relate a sibling student to an already
existing parent record. I would also need to retain the
ability to relate more than one parent record to a
student, to accomodate multiple parents in divorce
situations. I hope I've explained that clearly. How do
I accomplish that?

Thanks,

Mike

 

[Ken Snell]

Your form setup is just fine. What you want to do in the subform is use
combo boxes for displaying the parentID value for each record (or display
parent name while combo box is bound to the parentID field).

You don't need to "copy" a parent record. Just use the parentID with the
sibling's studentID as the pairing, just as you have for the first student.
Because the two studentID values are different, you can have the same
parentID value combined with the two different studentID values in different
records.