HAVING clause

[Rebecca]

Having trouble with HAVING clause.

Say my table shows families with one row for each kid, labeling that kid's
gender
So Jones family has 2 girls, 1 boy; Smith family has 2 boys, etc.

Jones / girl
Jones / girl
Jones /boy
Smith /boy
Smith / boy
Sanchez / girl
Sanchez /girl
Chang / boy

I want to ONLY get the families that DON'T have ANY girls. So my query
result should be Smith & Chang.

I did: HAVING (gender <>"girl")

But get an error message about it not being part of aggregate function.
Is there a way to make this work?

Note: I already did the query a different way, making girl=1 and boy =0,
instead of girl/boy word labels, and then just selected sum <1. (i.e., no
girls) But I still want to figure out how to use the HAVING clause in this
instance, if it's possible.

Thanks.

 

[Allen Browne]

Use a subquery:
http://allenbrowne.com/subquery-01.html

 

[John Spencer]

SELECT Family
FROM Table
GROUP BY Family
HAVING Sum(Gender="Girl") = 0

Any comparisons in a HAVING clause must include one of the aggregate functions
(or be in the group by clause). As noted elsewhere, you could use a sub-query
in a WHERE clause (not a HAVING clause) to limit the records returned.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County