Top 10 for each change in groupings

R

Ryan

I have a report which has the following fields:
Month, Division, Part Number, Part Description and Purchase Price Variance.

The report is grouped by Month and then Division. I want to show the top 10
parts ,sorted by Purchase Price Variance descending, for each change in Month
& Division. (There are 7 divisions so I should get 70 rows of data per
month). Can anyone help? Please
 
A

Allen Browne

In a report, this is probably easist to do with a subreport.

Create the main report so it gives only one record for each month +
division. For the subreport, create a query that gives the TOP 10 records
(in the Properties box, in query design.) The subreport based on this query
will then yield the top 10 records each time (i.e. for each of your 7
divisions.)

If you need to do it all in a query, a subquery can do it:
http://allenbrowne.com/subquery-01.html#TopN
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top