How do I show the 'top ten' in an Access report?

[Guest]

I have an Access database with 20,000 records. Let's say they are product
sales. Each sale has a geographic region. I'd like to be able to show the top
10 selling items (by value) in each region. I can sort my data in a report to
show the sales by product in descending order of value, but it runs to 700
pages! How do I just filter the top ten highest value items and put the
remainder as a single line item?

Thanks for your advice.

 

[Guest]

Try something like

Select * From TableName As T1
Where T1.[Value] In (Select Top 10 T2.[Value]
From TableName As T2 Where T2.[Region] = T1.[Region]
Order By T2.[Value] Desc)

You might end up with more then 10, incase there are two or more with the
same value.