Delete based on compare

[hdawn]

I'm sorry if this has already been asked, but I have not found it already answered entirely yet - so I'm hoping someone can help provide some clarification.

I have a delete query in access that I only want to delete certain rows based on if they sum to 0
Ex:
Tom -1
Jill 5
John -3
Tom 2
Jill -3
John 3

I only want to delete John's records because they sum to 0. I want to keep Tom and Jill's records.

I have the delete where exists written -- and it is good about not deleting if none sum to 0, but if even one person sums to 0 it's trying to delete all the records. How can I get it to delete only the ones that sum to 0? Any help would be greatly appreciated.

Here is what I have:

DELETE *
FROM File
WHERE EXISTS


(SELECT sum(File.amount) as sum, File.name
FROM File
GROUP BY File.name
HAVING (((sum(File.amount))=0)))

[ItsMe]

Have you tried building a separate subquery to manage the SUM() function? I typically don't use them but, a parameterized query may be in order here. The subquery that manages the SUM() function could be dependent on a combo in a form that would indicate the name criteria.

[hdawn]

Have you tried building a separate subquery to manage the SUM() function? I typically don't use them but, a parameterized query may be in order here. The subquery that manages the SUM() function could be dependent on a combo in a form that would indicate the name criteria.

Thanks for the reply... I may not have been doing it right, but sadly that is how I originally started. I ended up changing the where exists to a where in and removing the sum(file.amount) completely - leaving the having sum at the end and it is working. Not sure it's the best way to get to the data - but it works

[ItsMe]

If you start to see a performance issue while running the query then consider adjusting the SQL.