SOLVED

Query count and selection; wrong count

Contributor

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

4 Replies

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

 

ControlYearAmount
A120051000
A220062000
A120073000
A120094000

 

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

best response confirmed by Leonel GUZMAN (Contributor)
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.

Thx a lot!
So kind of you