How can I query the top 100 customers only?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a huge master database of sales for every one of our customers. There
is a sales territory number associated with every customer. Is there a way
to query just the top 100 customers per sales territory? (Example: we have
75 sales people so my output should be 75 x 100 records = 7500 records.)
 
Bill

Here's a couple approaches previous posted by one of the MVPs... (you might
have been able to "Google" this out if you'd searched for "Top ")

Build the report to sort and group by District and Profit. Place a text box
in the detail section:
Name: txtSequence
Control Source: = 1
Running Sum: Over Group
Then in code in the On Format event of the detail section
Cancel = Me.txtSequence > 5

Another method would be to use a subquery, something like (air sql):
SELECT *
FROM tblSales
WHERE ProductID IN (SELECT Top 5 ProductID FROM tblSales S WHERE
S.DistrictID = tblSales.DistrictID ORDER BY Profit DESC)
 
Back
Top