CODEWITHSUNDEEP
ms-access
Chrislaar123
Chrislaar123

Reputation: 321

How do I return the record with the max count

I have an access query which looks something like this; 

STOCK_CODE        DESCRIPTION               NAME             MaxOfCountOfNAME
100001  SLES - STEOL CS230-KE/TEXAPON   ALVIN CONNOR LTD          1
100001  SLES - STEOL CS230-KE/TEXAPON   NSO IS (DCSM GRADE)       12
100001  SLES - STEOL CS230-KE/TEXAPON   OREAN PERSONAL CARE LTD   1
100001  SLES - STEOL CS230-KE/TEXAPON   WILKINSON                 1
100001  SLES - STEOL CS230-KE/TEXAPON   ZEST AROMATICS LTD        18

However I would like to be able to only return the record with the maximum count which in this case is;

STOCK_CODE        DESCRIPTION               NAME             MaxOfCountOfNAME
100001  SLES - STEOL CS230-KE/TEXAPON   ZEST AROMATICS LTD        18

Any advice would help me a lot.

Many Thanks, Chris

Upvotes: 0

Views: 39

Answers (1)

Davis
Davis

Reputation: 250

Other languages have better options for this, but MS Access is a bit lacking. Unless you have a specific need to only return the maximum I would suggest that you just order by the descending value of that column.

The solution to make this work tends to be to use sub-queries to create a filter. Also, please note that you will not necessarily only retrieve one record with this. It will return all results that have a MaxOfCountOfNAME matching the largest value.

I'll assume that the query you provided is a saved query and reference it as [query]

SELECT q.STOCK_CODE, q.DESCRIPTION, q.NAME, q.MaxOfCountOfNAME
FROM [query] q
WHERE MaxOfCountOfNAME = (SELECT MAX(MaxOfCountOfNAME) FROM [query])

Upvotes: 1

Related Questions