Top 10 for each change in groupings

  • Thread starter Thread starter Ryan
  • Start date Start date
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
 
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
 
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
 
Back
Top