Forum Discussion
Query count and selection; wrong count
Thank you all
I have these queries (below) and neither is giving me the expected result.
Some records have the same [CONTROL] number, and I was expecting the query would group them and show the count. The count should be 5, but the result shows 4 only for some records, others are indeed 5. In the origin table (qry_AllRecords) indeed the list has 5 items. Both queries are showing the same result.
All help appreciated
Thx
SELECT qry_AllRecords.CONTROL, Count(qry_AllRecords.Control) AS CountOfControl
FROM qry_AllRecords
WHERE (((qry_AllRecords.CONTROL)=[CONTROL]))
GROUP BY qry_AllRecords.CONTROL;
I have also tried with
SELECT qry_AllRecords.CONTROL, Count(qry_AllRecords.Control) AS CountOfControl
FROM qry_AllRecords
GROUP BY qry_AllRecords.CONTROL
HAVING (((qry_AllRecords.CONTROL)=[CONTROL]));
Leonel GUZMAN There may be a more "elegant" way to do it, perhaps. However, I'm a firm believer in the notion that if it works, and is acceptably efficient, that's what matters.
Leonel GUZMAN Often, seeing the actual data helps. Also, seeing the SQL in the intermediate query also helps.
Thank you George Hepworth
I have 5 different tables merged with a UNION ALL q (qry_Union). The result from qry_Union looks like this:
Control Year Amount A1 2005 1000 A2 2006 2000 A1 2007 3000 A1 2009 4000 I need to group [Control] when it is present in all 3 years (A1 in the example).
I have managed to get the right result now with this:
SELECT qry_Union.CONTROL, Count(qry_Union.Control) AS CountOfControl
FROM qry_Union
WHERE (((qry_Union.CONTROL)=[CONTROL]))
GROUP BY qry_Union.CONTROL
HAVING (((Count(qry_Union.Control))=3));However, I guess there's probably a smarter way of doing it.
Thank you
Leonel GUZMAN There may be a more "elegant" way to do it, perhaps. However, I'm a firm believer in the notion that if it works, and is acceptably efficient, that's what matters.
