Delete Dupes within table

Topic

New Reply

Hello. (Access 2007)
I have a table of staff (tblStaff) including StaffID field, and a second table tblLeavers with a few of these staff members. How can I create a delete query that
will delete those staff in the tblStaff that appear in the second table?
I think this should be very simple, but I can’t get it to work?! Thanks greatly, Andy.

Reply | Quote

Replies

Try a query with this SQL:

DELETE * FROM tblStaff WHERE StaffID In (SELECT StaffID FROM tblLeavers)

Reply | Quote

Hi. That’s great. (I got there two seconds before I read your answer – must be telepathy?)
But why? Why can’t I just link the two tables in Query Design and delete * from tblStaff WHERE StaffID = tblLeavers!StaffID? Andy.

Reply | Quote

But why? Why can’t I just link the two tables in Query Design and delete * from tblStaff WHERE StaffID = tblLeavers!StaffID? Andy.

It depends on the way the tables are set up. If the query as a select query is not updateable, you won’t be able to use it as a delete query.

Reply | Quote