Deleting records from one table based on records in another.

[MrPiccolo]

Hi folks,
maybe i'm being dense(?!), but i need to delete 596 records from table #A using a list of corresponding record id's held in table #B as the criteria. So far Access won't allow me to run a delete query to perform the task.
(In other words both tables have identical record id formats but table #A holds thousands of records but the other (table B#) holds only 596.)

Any suggestions please as how to do this task??
Many Thanks
MrP

 

[alvechurchdata]

So far Access won't allow me to run a delete query to perform the task

Sounds as though a Delete Query is what you want. Why won't Access let you run it?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

Are you saying the following is not working for you ?
DELETE A.* FROM [table #A] AS A INNER [table #B] AS B
ON A.[record id] = B.[record id]

An alternative:
DELETE FROM [table #A]
WHERE [record id] In (SELECT [record id] FROM [table #B])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrPiccolo]

Hi Geoff,
The problem seems lie in the fact that there are two tables (linked by a 1-2-1 join), in the QBD; which causes the delete query to throw up the error message "specify the table to delete from" when a run query is initiated.
mrP

 

[MrPiccolo]

Thanks PH,
Your alternative method solved the problem nicely!
Mucho Gracias
MrP