Forum Discussion
Query count and selection; wrong count
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.
- Thx a lot!
So kind of you