Selecting top 5

[hotplate]

Hi,

Can someone give me an example of this query?

I have 4 different commodities. Each commodity has several different
suppliers. I am counting the number of times a supplier's number
occurs within a period of time. I am trying to limit each commodity
to the top 5 results.

When I do a select top 5, I just get the 5 highest counts. I need the
top 5 per each commodity (for a result of 20 records).

Does anyone have an example of how to do this?

James

 

[Douglas J. Steele]

Check what Allen Browne has at
http://www.allenbrowne.com/subquery-01.html#TopN

 

[ntc]

well I think you are almost there....but not sure you can get it just inside
one single query alone...though maybe an mvp will have a way....

I would use a form or report to aid in achieving the goal....inset that
query as a subform/report where the parent form/report is based on a
table/query with just the commodities....using the commodity ID to link
parent/child of course


I think that may work though don't use topX very often....hopefully so as
otherwise chopping them into individual queries could be a bit of work if
there are alot of commodities...