Joe Hecht
jmhla at earthlink.net
Tue Dec 28 23:00:42 CST 2004
I am short on time to deadline. Have chosen to allow client to make employee inactive. Then related records just wont show in reports.. JOE HECHT LOS ANGELES CA jmhla at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: Tuesday, December 28, 2004 6:04 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Relationship Issue It's done in the actual relationship in the BE. Open the BE. Click Tools / relationships Find the relationship between Employees and stblEmpJobs Dbl click the line for the relationship, or right click and select edit relationship from the right click menu. Click Cascade Delete related records. Hang on to your pants. Deleting records from a database is rarely a good idea. If you are going to do so, then an archive of all affected records is a must. I would doubt that this is the only sub-table of an employee. The client will likely be carving huge chunks of data out of their database every time they delete an old employee. For the rest of eternity you will be hearing "what happened to that record in table XYZ". In my Insurance call center database for example, the employee ID is logged in no fewer than 7 tables - Who is responsible for a specific policy, who created the claim record, diary records, claim event records, claim contacts, claim notes, Policy responsible employee, mail merged letters etc. Deleting any one of those employees cannot be performed without also deleting all the related records in all those tables. MASSIVE pieces of history about the claims would be missing. In fact however it gets worse, the employee cannot be deleted without the claim being deleted. The claim is parent to payment records, ATPs, NCNs etc which do not have an employee ID specifically associated with it. Likewise in this specific case, there is a "who is responsible for this policy" field. Thus deleting the claimant will force deleting the policy, and all the claims under the policy. Deleting a single employee would force deleting thousands of records in dozens of tables. NEVER a good idea, and then ONLY with an archive system in place. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Hecht Sent: Tuesday, December 28, 2004 8:29 PM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Relationship Issue I have a table Employees and a subtable stblEmpJobs. They link on field EmpID The link was built when I built and dropped the forms together. The relationship in the window says one to many ( external ). I need to edit the relationship to enforce referential to do permit cascade deletes. Any ideas ? The customer has signed off they do not want to archive ex employees in the system. She wants to pull the paper records JOE HECHT LOS ANGELES CA jmhla at earthlink.net -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com