Despite extensive searching here on EE, I have yet to figure this one out (I'm also sort of a newbie):
I'm trying to write a query that's grouped by several fields, but I also want to generate a few summary statistics of other fields along the way. In particular, I want records where UVA = 1 to be counted and I want records where UVA = 2 to be counted -- both separately. But I don't know where to put the "where"! Here's what I have so far:
SELECT PubPri([ownership]) AS PrimOwn, tblGenData.Ownership, Count(tblIntData.UVA) AS YesUVA WHERE tblIntData.UVA=1, Count(tblIntData.UVA) AS NoUVA WHERE tblIntData.UVA=2, Avg(tblIntData.THOArea) AS AvgArea, Sum(tblIntData.THOArea) AS TtlArea
FROM tblGenData INNER JOIN tblIntData ON tblGenData.pkOpNum = tblIntData.pkOpNum
GROUP BY PubPri([ownership]), tblGenData.Ownership;
And if I build the query in design view and then toggle to SQL, when I indicate criteria of 1 and 2 for the fields YesUVA and NoUVA, Access sticks in "HAVING (((Count(tblIntData.UVA))=
1) AND ((Count(tblIntData.UVA))=2
));" at the end of the SQL statement, but I want those criteria to occur as part of the count.
Thanks for your help!