Delete query using criteria from another table

[brightday]

I have two tables (Master Asset List & Asset Versions Lists) with one common field (Asset_ID). I am trying to create a delete query that will delete a record in the Master Asset List of there is no corresponding record in the Asset Versions List.

I used the Query Wizard to create a 'Find Unmatched' query. This correctly returns the results I want to delete. I opened this query in design view, and changed it to a delete query by clicking Delete in Query Type on the Design Ribbon. I thened add the table I wanted to delete the record from (Master Asset List) with the field as "*" and changed the Delete setting to 'From' (see attached image).

When I run this query, I am warned that "You are about to run a delete query that will modify data in your table" and I am propted to confirm if I wish to continue (Yes/No/Help). When I confirm, I get the error message "Specify the table containing the records you want to delete".

Any suggestions on what I should do to fix this?

 

[Jeanette Cunningham]

Use a sub query to delete, like this:

DELETE [Master Asset List].AssetID
FROM [Master Asset List]
WHERE ((([Master Asset List].AssetID) In (SELECT [Master Asset List].AssetID
FROM [Master Asset List] LEFT JOIN [Asset Versions List] ON [Master Asset List].AssetID = [Asset Versions List].AssetID
WHERE ((([Asset Versions List].AssetID) Is Null))));