stitch70 stitch70 - 7 months ago 24
SQL Question

Access - VBA - SQL Query for All Records

Date Name Charge
1/15/2015 Alpha 0
2/15/2015 Alpha 0
2/15/2015 Beta 0
2/15/2015 Gamma 2
3/15/2015 Gamma 3
3/15/2015 Alpha 5
4/15/2015 Beta 0


Above is a sample table in Access. My intent is to capture all unique Names that have strictly value 0 for 'Charge', but if any name has a non-zero value, then the query would not return the Name at all.
For example, based on sample data, a correctly write query would result in only "Beta" showing up.

How can a SQL query be written for the above requirements? Appreciate the help - thanks!

Answer

You can use SUM along with GROUP BY and HAVING:

SELECT Name
FROM tbl
GROUP BY Name
HAVING SUM(Charge) = 0

ONLINE DEMO