How do I filter for top 10 values?

[Guest]

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?

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Can you post your SQL, and what is the result that you got

 

[Brendan Reynolds]

You might find the following resources helpful. The first is a GUI-oriented
introductory article that does not assume any previous experience with SQL.
The second has the down-and-dirty technical details.

Find the records with top or bottom values in a field
http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051880331033

ALL, DISTINCT, DISTINCTROW, TOP Predicates
http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP010322051033