Query count and selection; wrong count

[Leonel GUZMAN]

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]));

[#2]

@Leonel GUZMAN Often, seeing the actual data helps. Also, seeing the SQL in the intermediate query also helps.

[#3]

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

[#4 · Accepted solution]

@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.

[#5]

Thx a lot!
So kind of you