Access - VBA - SQL Query for All Records

Question

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!

I'm updating this where instead of 0, I'm looking for charges strictly equal to 3.39. How would a SQL query be written? I realize SUM would not work for this request, and Access is limited in the number of functions available. — stitch70
– stitch70, Commented May 20, 2016 at 20:56

Community · Accepted Answer · 2021-01-18 12:31:40Z

2

You can use SUM along with GROUP BY and HAVING:

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

ONLINE DEMO

edited Jan 18, 2021 at 12:31

CommunityBot

11 silver badge

answered May 5, 2016 at 4:37

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

stitch70 Over a year ago

Felix - this is great...thank you, but could greater flexibility be coded in? For example, assuming 5/15/2015 was a constant value, and the user entered "2", then the query would only apply to strictly data 2 months back, being from 3/15/15 onwards. Or if user entered "3", then the query would only apply to strictly data 3 months back, being from 2/15/15 onwards.

stitch70 Over a year ago

In following up, is there a simple way to include 2 conditions for the HAVING criteria? I'm looking for Sum(Charge) = 0 and Sum(Charge) = 5

stitch70 Over a year ago

I'm updating this where instead of 0, I'm looking for charges strictly equal to 3.39. How would a SQL query be written? I realize SUM would not work for this request, and Access is limited in the number of functions available.

stitch70 · Accepted Answer · 2016-05-05 16:06:49Z

0

I think I may actually have the solution (below) being this to my own question. It's instead requesting user to enter start date & end date as inputs:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tbl.Name
FROM tbl
WHERE ([tbl].[Date]>=[Start Date] And [tbl].[Date]<=[End Date])
GROUP BY tbl.Name
HAVING SUM(Charge) = 0;

edited May 5, 2016 at 16:06

answered May 5, 2016 at 15:46

stitch70

1431 gold badge4 silver badges15 bronze badges

1 Comment

stitch70 Over a year ago

I'm updating this where instead of 0, I'm looking for charges strictly equal to 3.39. How would a SQL query be written? I realize SUM would not work for this request, and Access is limited in the number of functions available.

Collectives™ on Stack Overflow

Access - VBA - SQL Query for All Records

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related