Still need help on Many to Many Relationships

[Mike]

Hello,

I received some help before, but it did not address the
issue fully.

I 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
parents table. However, in instances where there are
sibling students, I have had 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 have created a juntion table with the studentID and
parentID as primary keys. Do I have to populate that
table with all of the data from both the student and
parent tables and then use that as the underlying data
source for the form? Or is there a better way to utilize
this many to many relationship? Please adivse.

Thanks,

Mike

 

[Jeff Boyce]

Mike

I haven't followed your earlier thread, so this may be a repeat of something
already suggested.

First, forget about your forms until your tables are in order. Your tables
need to represent entities, and their attributes.

From what you did describe, you might be able to get away with a single
table for Persons. Each person, whether in the role of student or parent or
....? would have a single row in tblPersons, with as many fields as needed to
describe the person (first, middle, lastnames at a minimum, maybe DOB).

Next, it seems like you'd need a way to show a family unit, and all it's
members and their roles. One way to do this is to create a
trelFamilyMembers, with a FamilyID field (NOT an Autonumber), a PersonID
(from the tblPerson), and a RoleID (from a small lookup table you create,
holding at least "Student" and "Parent"). By adding multiple rows with the
same FamilyID but different PersonIDs & RoleIDs, you've just indicated all
the members of a family.

To find Parents from Students, build a query that looks for PersonID in the
trelFamilyMembers where RoleID is Parent, and where the FamilyID = the
FamilyID that goes with the PersonID of the Student in question.

You could also do the reverse (Find Students from Parents).

Once you have tables like these, and the relationships established, post
back to the .forms newsgroup for more ideas on building the application.

Good luck!

Jeff Boyce
<Access MVP>