Help with a "Top Values" Query

[thenoisydrum]

Good Morning,
I am designing what is in theory a very basic set of queries but I am stuck on the "Top Values" element.
The scenario;
Basically I need to list the top 20 selling products by brand.
There are multiple brands and I am leaving this field blank in order to capture all of them. Problem is, when I select the Top Values as 20 in the properties and run the query it just selects the first 20 from the first brand (that appears in alphabetical order). Alternatively if I add a descending filter to the sales volume then it gives me the top 20 sellers across all the brands.
I need to show the top 20 sellers by brand.

Is this simple to do?
As always your help would be appreciated.

Regards

'Drum

 

[Brianwarnock]

"Top" only works across the whole file, you need to do a ranking and select what is required.

See if this helps

http://www.access-programmers.co.uk/forums/showthread.php?p=967646&highlight=Rank#post967646

Brian

 

[Brianwarnock]

Here is another one

http://www.access-programmers.co.uk/forums/showthread.php?p=967646&highlight=Rank#post967646


No doubt searching the forum on rank will give more

Brian

 

[jdraw]

Further to Brian's examples, here is another.
see Top N in this link
http://allenbrowne.com/subquery-01.html

 

[thenoisydrum]

Brian, thanks very much for your help but I couldn't get it to work. I went with the Top n Value example on the AllenBrowne website in the end - thanks very much jdraw.

'Drum

 

[Brianwarnock]

No problem you have got to go with what works for you.

Brian