Delete query using criteria from another query

[skipatco]

I would like to create a delete query that uses a calculated value from
another query as the criteria. When I set up the query initially as a select
query, it shows the correct records that I would like to delete. When I turn
it into a delete query and run it, it will not delete "could not delete from
specified tables". I have full access the tables, none are read only etc.

Settup:
table1 query1
ems_kit_id ems_kit_id
lot_number lot_number
number_assigned number_in_kit
number_assigned
total_used

ems_kit_id from both are joined
lot_number from both are joined

Query1 calculates number_in_kit based on number_assigned and total_used

Field:table1.* number_in_kit
Table:table1 query1
Delete:From where
Criteria: 0

thanks

 

[Allen Browne]

Use a subquery in the WHERE clause:

DELETE FROM Table1 WHERE
(SELECT ... FROM ... WHERE ...) = 0;

If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html