Delete Query with Requirements

[soberman]

Hi all. I am trying to build a delete query that will delete a
household (single record) in a table IF AND ONLY IF the balance for
that household is zero. The balance information is located in a
calculated field in a query. How do I instruct the delete query to
verify a zero balance in the balance query before deleting the record
in the household table?

 

[John Spencer]

Generically, that would look something like the following.

DELETE SomeTable.HouseHoldIDField
FROM SomeTable
WHERE HouseHoldIDField = "Something" AND
HouseHouldIdField in (SELECT HouseholdIDField From YourSummaryQuery
WHERE SumOfCharge = 0)