SOLVED

Query count and selection; wrong count

%3CLINGO-SUB%20id%3D%22lingo-sub-1330370%22%20slang%3D%22en-US%22%3EQuery%20count%20and%20selection%3B%20wrong%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330370%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20these%20queries%20(below)%20and%20neither%20is%20giving%20me%20the%20expected%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20records%20have%20the%20same%20%5BCONTROL%5D%20number%2C%20and%20I%20was%20expecting%20the%20query%20would%20group%20them%20and%20show%20the%20count.%20The%20count%20should%20be%205%2C%20but%20the%20result%20shows%204%20only%20for%20some%20records%2C%20others%20are%20indeed%205.%20In%20the%20origin%20table%20(qry_AllRecords)%20indeed%20the%20list%20has%205%20items.%20Both%20queries%20are%20showing%20the%20same%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20help%20appreciated%3C%2FP%3E%3CP%3EThx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20qry_AllRecords.CONTROL%2C%20Count(qry_AllRecords.Control)%20AS%20CountOfControl%3CBR%20%2F%3EFROM%20qry_AllRecords%3CBR%20%2F%3EWHERE%20(((qry_AllRecords.CONTROL)%3D%5BCONTROL%5D))%3CBR%20%2F%3EGROUP%20BY%20qry_AllRecords.CONTROL%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20tried%20with%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20qry_AllRecords.CONTROL%2C%20Count(qry_AllRecords.Control)%20AS%20CountOfControl%3CBR%20%2F%3EFROM%20qry_AllRecords%3CBR%20%2F%3EGROUP%20BY%20qry_AllRecords.CONTROL%3CBR%20%2F%3EHAVING%20(((qry_AllRecords.CONTROL)%3D%5BCONTROL%5D))%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1330370%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330467%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20count%20and%20selection%3B%20wrong%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F216718%22%20target%3D%22_blank%22%3E%40Leonel%20GUZMAN%3C%2FA%3E%26nbsp%3BOften%2C%20seeing%20the%20actual%20data%20helps.%20Also%2C%20seeing%20the%20SQL%20in%20the%20intermediate%20query%20also%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331132%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20count%20and%20selection%3B%20wrong%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331132%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%205%20different%20tables%20merged%20with%20a%20UNION%20ALL%20q%20(qry_Union).%20The%20result%20from%26nbsp%3Bqry_Union%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EControl%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EYear%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EAmount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2005%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E1000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2006%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA1%3C%2FTD%3E%3CTD%3E2007%3C%2FTD%3E%3CTD%3E3000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA1%3C%2FTD%3E%3CTD%3E2009%3C%2FTD%3E%3CTD%3E4000%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20group%20%5BControl%5D%20when%20it%20is%20present%20in%20all%203%20years%20(A1%20in%20the%20example).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20managed%20to%20get%20the%20right%20result%20now%20with%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20qry_Union.CONTROL%2C%20Count(qry_Union.Control)%20AS%20CountOfControl%3CBR%20%2F%3EFROM%20qry_Union%3CBR%20%2F%3EWHERE%20(((qry_Union.CONTROL)%3D%5BCONTROL%5D))%3CBR%20%2F%3EGROUP%20BY%20qry_Union.CONTROL%3CBR%20%2F%3EHAVING%20(((Count(qry_Union.Control))%3D3))%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20guess%20there's%20probably%20a%20smarter%20way%20of%20doing%20it.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332152%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20count%20and%20selection%3B%20wrong%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F216718%22%20target%3D%22_blank%22%3E%40Leonel%20GUZMAN%3C%2FA%3E%26nbsp%3BThere%20may%20be%20a%20more%20%22elegant%22%20way%20to%20do%20it%2C%20perhaps.%20However%2C%20I'm%20a%20firm%20believer%20in%20the%20notion%20that%20if%20it%20works%2C%20and%20is%20acceptably%20efficient%2C%20that's%20what%20matters.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332266%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20count%20and%20selection%3B%20wrong%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332266%22%20slang%3D%22en-US%22%3EThx%20a%20lot!%3CBR%20%2F%3ESo%20kind%20of%20you%3C%2FLINGO-BODY%3E
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