Referential integrity in many-to-many?

[Bruce]

I have a database for keeping track of training. Most
training is done in house as needed. For instance, a
revised manufacturing procedure necessitates training. My
database consists of three tables: a Sessions table for
recording training session information (Subject,
Department, Instructor, etc.); an Employees table (which
is fairly static unless somebody is hired or leaves) for
the usual reasons; and an Enrollment table for recording
attendance (name and date, mostly) at the training
sessions. SessionID is PK of tblSession and FK of
tblEnrollment. EmployeeID is PK of tblEmployee and FK of
tblEnrollment. In other words, tblEnrollment is a
junction table between tblSession and tblEmployee. Each
employee will attend many training sessions, and each
training session will have many attendees. If there are
five attendees at training session 99 (the PK), there will
be five records in tblEnrollment with FK 99. Each of
these five records will have a different EmployeeID FK.
My main form, frmSession, is based on tblSession. It
contains a subform (fsubEnrollment) that is based on
tblEnrollment. It all works quite well, and I can
generate reports by employee and by session, as intended.
However, in reviewing the relationships I learn that I
have violated referential integrity rules in tblEnrollment
(the junction table). This message occurs when I attempt
to enforce referential integrity between tblSession and
tblEnrollment (one-to-many). However, I can enforce
referential integrity between tblEmployee and
tblEnrollment (also one-to-many). Can anybody tell me
what is going on with this? Does it have anything to do
with tblSession being populate on the fly as new training
sessions are recorded, while tblEmployee is populated
separately? That is to say, the names in fsubEnrollment
are selected from a combo box based (by way of a query) on
tblEmployee. If the details above are not sufficient, I
can provide more, but I am trying to keep this question
compact.
One more (unrelated question): When the database opens I
have it set to open at a new record. Most of the time
that is what is needed. However, sometimes the user will
search for an old training session, and things like that.
When that happens, and no new record is added, it will
skip to the next autonumber PK in tblSessions. It will
also happen if somebody opens and then closes the
database. I could avoid some of that nuber skipping by
using a startup form that would open the main form to
either the last record or a new record, depending on
whether the intention is to search old records or add new
ones. Otherwise the main form would be the same, with
search and add options available in either case. Is there
any reason to go to this extra step?

 

[Douglas J. Steele]

I'm assuming you already had some data in your tables before you tried to
apply RI. If so, the problem's probably that you have some EmployeeID value
in tblEnrollment that doesn't exist in tblEmployee.

 

[Bruce]

Thanks for your reply. I was away for the holiday
weekend, and when I returned a lot of things awaited me,
so I didn't get a chance to check the tables until today.
To sum up the database, there is an Employees table
(autonumber PK, Name, etc.), a Session table (autonumber
PK, subject, department, etc.), and a junction table
(Enrollment) with FKs corresponding to the PKs from the
other two tables, along with Date, etc.). The Employees
table is relatively static, but the Sessions table changes
every time a training session is logged. With every entry
in the Session table there is at least one entry (attendee
at the session) in the Enrollment (junction) table.
The problem was that I had removed some sessions that were
entered in error, but did not remove the corresponding
attendance records from the Enrollment table. Once I
cleaned that up I was able to enforce RI. I also checked
the box to cascade delete related records. I did not
check the same box in the Employees table to Enrollment
table relationship, as I am not yet sure how we will
handle the records of former employees. There will
probably be some sort of archiving, but not for a while.
Any thoughts on archiving would be appreciated. The
Enrollment table in particular could get pretty large
after a while.
Thanks again for getting me pointed in the right direction.