Hello,
I have a junction table that ties together a Member table (PK: MembID) and a Course table (PK: CurricID). (One student has many courses; one course has many students)
The junction table, at this point, has a Composite Primary Key composed of the above two keys -- with MembID set to Index/NoDup and CurricID set to Index/DupsOK
The fields of the junction table are these two keys, semester, year, -- and then a multi-value field: CoursesChosen, with the "sub-field" of CoursesChosen.Value
A form with member information, with a subform with the course information, is used to enter the data. These are joined on MembID, parent and child.
The problem I am trying to solve is that my data entry person somehow created two records in the junction table for the same person with the same id.
In trying to figure out how this could happen, I noticed that I had no primary fields in the junction table!
Now I have made the composite primary key, as described, but I don't know if I have solved the problem or if this is correct.
My relationships are now as follows:
MembID to MembID, one to one, include all records from the junction table, no referential integrity. (Meaning that to delete a person, I would delete their courses first.)
And: CurricID in the Course table relating to CoursesChosen.Value, One to Many, Include all records in the Junction table, Enforce referential Integrity/Update Related fields.
So, the Junction table key CurricID seems useless. is this right?
I am getting pretty confused! Any help would be appreciated, specific and in general. Thanks!