How do I filter for top 10 values?

2

2005alistairg

I have a table that contains the following colunms - Rep, product, sell,
range, & account. Each account has only one rep & I have several reps. Each
product has only one range & I have several ranges.
I am trying to get a report to show the Top 10 products (by sell value)
within each range and for each account.
Any suggestions?
 
O

Ofer

Try this

SELECT M1.Rep, M1.product, M1.sell, M1.range, M1.account
FROM TableName AS M1
WHERE M1.sell In (SELECT Top 10 M2.sell
FROM TableName as M2
WHERE M2.product=M1.product AND M2.range=M1.range And M2.account=M1.account
ORDER BY M2.sell Desc)
 
2

2005alistairg

Hi Ofer
Thanks for the reply, although I'm not completely sure what it all means I
will give it a go!
 
D

dlw

make a new query, design view, pick the table, from menu bar view/sql view,
and type that stuff in
 
2

2005alistairg

Hi
I have followed the instructions but the query results do not give the top
10 products for each account within each range. It may well be how I asked
the question!
an example of what I'm trying to do is
Range 21
Account = ABC Ltd
1st product £100
2nd product £90
3rd product £50
etc
etc

Account = XYZ Ltd
1st product
2nd product
3rd product
etc
10th product

Range 22
Account = ABC Ltd
1st product
2nd product
etc etc

I hope that may be a bit easier to understand.

Thanks
 
Top