many-to-many relationship to a single table

[roleic]

Situation:


I have a table "Pupils" with a primary key "pupil" and have the entries john, mary, billy, susan, marc in it.

Now I would like to enter the information that john, billy and susan are siblings.

So I created another table "Siblings" with two combined primary keys "pupil_A" and "pupil_B". Then I introduced a 1-to many relationship from pupil in table Pupils to pupil_A and onother one from pupil in table Pupils_1 (instance of Pupils in the relationship window) to pupil_B.
This worked perfectly. In Siblings I can enter now john and billy, john and susan, billy and susan.

Problem:
What disturbs me is that I can without any error message also enter the reverse relationship billy and john which is a redundant entry to john and billy. Moreover I can also enter john and john.

Question:
How can I prevent the above two kinds of entries?

[ConneXionLost]

You will need three tables. Use your original "tblPupil" table, a "tblFamily" table, and a "tblFamilyMember" table.

In tblFamily, you would have FamilyID (PK), Surname, etc. The data would/could be: Smith, Jones, Miller, etc.

In tblFamilyMember, you would have FamilyID & PupilID as a composite PK.

Cheers,

[jameslarsen]

Most likely you will need to do this with code. Access cannot interpret data, and doesn't understand that John / Susan is the same as Susan / John, or that John cannot be joined to himself. What I have done in similar situations is to build an interface to add new people, and as part of the add procedure, it would take the two names, and run a few VBA queries. First it would check if the two names matched each other. Assuming they did not, it would then run a query that reversed the names, and see if any results were returned. If no results came back, only then would it execute an insert statement into the "Siblings" table with the IDs of the two people being linked. Unfortunately I don't know of any easy ways to accomplish this using the Access GUI builder.