If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Referential integrity in many-to-many?
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? |
#2
|
|||
|
|||
Referential integrity in many-to-many?
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Bruce" wrote in message ... 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? |
#3
|
|||
|
|||
Referential integrity in many-to-many?
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. -----Original Message----- 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Bruce" wrote in message ... 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? . |
Thread Tools | |
Display Modes | |
|
|