Top 10 for each change in groupings

[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

 

[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

 

[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