Forum Discussion
Query count and selection; wrong count
- Apr 23, 2020
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.
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.
- Leonel GUZMANApr 23, 2020Brass ContributorThx a lot!
So kind of you