best sellers by vendor

  • Thread starter Thread starter Maureen
  • Start date Start date
M

Maureen

Good morning,

From invoice detail I would like to collect (in a single query) the top 5%
for each vendor. i.e.

Vendor 1 has 100 items I'd like the top 5
Vendor 2 has 1000 items I'd like the top 50
Vendor 3 has 500 items I'd like the top 25

I'd like to collect sales for the last two months (invoice date) sum unit
sales and select a single list sorted/grouped based on the criteria above. I
am thinking that I'll set up a table that will be updated with the qty's as
above to start and then have separate queries for each vendor. Any possible
way to consolidate this into one or two queries?
M
 
Hi,



with a correlated sub query:

SELECT a.*
FROM myTable as a
WHERE a.primarykey IN( SELECT TOP 5 PERCENT b.primaryKey
FROM myTable As b
WHERE b.vendorID=a.vendorID
ORDER BY b.itemsSale DESC)
ORDER BY a.vendorID, a. itemsSale DESC




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top