Deleting record from a select query from two tables... which table is affected?

1
votes

I have two tables that join a relationship.

  1. tblEmployeeDetails
  2. tblEmployeeDocumentExpiry

Tables are joined as one to many with Enforce Referential Integrity is selected.

I have a query which combines data from both tables [queryEmployees].
I have a form to enter/update/delete data onto that query [frmEmployeeMgmt].

My problem is only with deleting records from that query. When I delete a record, it gets deleted from one table only [EmployeeDocumentExpiry]. I am looking for a scenario that when a record is deleted from that query, it gets deleted from both tables.

Any ideas please. https://i.stack.imgur.com/WbWMb.png

2
sqlms-accessvba
Maybe I should have asked: Is it normal/ok to delete rows of tables from a select query? - user8584469
If you have a one to many referential integrity, why do you want to delete the "one" record when you delete a "many "record? I can understand that you want to delete all the documents when you delete the employee, but I don't get why do you want to do the opposite. - Alberto Martinez
I really want to delete the employee (the one record) from the query. But Access deletes the many record and keeps the one. In other words, the delete command is for deleting the employee. It seems that I cannot delete an employee record except from the table. Deleting a record from query which combines a one table and a many table will delete the many record and keeps the one. Is it possible to change the one to many relationship to one to one? - user8584469
I understand now, you can't change the relationship to one to one because in that case you only can have one document per employee. Anyway, you don't need a query to do that, you can configure the relationship to automatically deleting the "many" records when deleting the "one", I'll put an answer with that. - Alberto Martinez

2 Answers

0
votes

I think your two tables needed to have the the same foreign key to execute the delete, but it will delete all docs which have that EmpID.

delete tblEmpDetails, tblEmpDocExp from tblEmpDetails inner join tblEmpDocExp where tblEmpDetails.EmpID = tblEmpDocExp.EmpID and tblEmpDetails.EmpID = '999';

OR in two statement as below

delete from tblEmpDetails where tblEmpDetails.EmpID = '999'
delete from tblEmpDocExp where tblEmpDocExp.EmpID = '999'

Hope this will help.

0
votes

If you have a one to many relationship and want to delete all the records related when you delete the "one" record you can configure it the relationship properties: select the relationship, double-click it to show the its properties and check "Cascade Delete Related Records".

With this option enabled then you just delete the record from the employee table and instead of failing with a referential error the DB will deleted all the records related to that employee (use it carefully).

enter image description here