Best way to capture many-to-many relationship

[Buakaw]

Hi,
I have been tasked to do some modifications to a database in my school that was coded by someone else who's no longer with us. I have no previous experience with Access and have only started picking it up and need help.

It's a student database that captures information of students and their parents (name, date of birth, address, contact number, that kind of thing). The problem with the existing setup is that it uses a 1 (student) to Many (parents) relationship.

Say, student Jack has a mum and dad. His mum is Jill and his dad is John. So the previous person used a 1(Jack)-to-many(John and Jill) relationship. The problem with this is that Jack can have a sister Sally, so Sally's parents are also John and Jill. This gets very messy for parents with many children all attending our school.

This creates a minor problem as it introduces duplicates in the parent's information table. I'd like to eliminate this by making it a many-to-many relationship. One student can have many parents, and each parent can have many children.

My problem comes when capturing this information. We have an existing Access Form which the previous staff created, for new students and their parents to key in their information.



This won't be a problem for a parent registering for the first time. But how am I going to capture a parent who's registering her second or third child, and I don't want their records to be duplicated in the Parent's table?

The only thing I can think of is selecting their names from a drop-down box which lists existing parents. But how do I make it generic, so that new parents can also key in their information?

Thank you!

[RuralGuy]

Maybe you will find this link helpful: http://www.fontstuff.com/access/acctut20.htm

[Dinzdale40]

instead of a parent table where a kid can have two parents that are two records, you could make it a "guardian" table with records consisting of multiple contacts. Each guardian entry would have a primary key that would be listed for each kid making it so that you could still use a one to many relationship. This may be a better way to relate parents and situations that are unique. Some parents could be divorced representing two guardian records where as some kids could have guardians that aren't their parents at all.

[RuralGuy]

This link may also provide some thoughts: http://allenbrowne.com/AppHuman.html

[Buakaw]

Thanks for the links, RuralGuy. That's the kind of thing I was looking for. I'll give it a try and come back if I encounter any problems. Thanks!

[Buakaw]

instead of a parent table where a kid can have two parents that are two records, you could make it a "guardian" table with records consisting of multiple contacts. Each guardian entry would have a primary key that would be listed for each kid making it so that you could still use a one to many relationship. This may be a better way to relate parents and situations that are unique. Some parents could be divorced representing two guardian records where as some kids could have guardians that aren't their parents at all.

Yes, that's what I have now. A separate student and guardian table. But it doesn't solve the problem because there will be duplicate guardian records in the guardian table.

E.g. John's father is Bob Jones. So Bob Jones is entered into the Guardian table once
.
Later, Jill comes along and her dad is also the same Bob Jones. Bob Jones then gets a duplicate entry into the guardian table, and is listed there twice.

Thanks.