Deleting records based on a Select query 1

[Cads]

Hi
A database I created a couple of years ago has accumulated some records which should have been deleted by the system (bad design!). What I've decided to do is construct a select query to identify these records and then use this query to delete the offending records and so tidy things up.

However.......

When I create a new query linking the original table containing the records to delete with the select query of 'rogue' records, the delete query that's created comes back with the error message "Could not delete from specified tables". The help system talks about read-only databases and the like but that isn't applicable. To assist, I include the SQL to give you a better understanding of what I'm trying to do:

DELETE Organisations.*
FROM Organisations INNER JOIN tmpqryNoConMem ON Organisations.OrgID = tmpqryNoConMem.OrgID;

The table and the query links 1:1.

Any ideas why SQL / Access doesn't like this?

Cheers!




Steve House
shouse@icaew.co.uk

http://www.icaew.co.uk

 

[jitter]

You might try:


DELETE FROM Organisations
Where OrgID IN (Select OrgID From tmpqryNoConMem);