Group Top N Values in a Report

[bg1907]

I've seen this posted time and again and I still can't get it right... I
have two fields; BrokerFk and GrossComm. BrokerFk is a Broker's Number and
GrossComm is the Commission the broker makes. I am trying to create a report
that is grouped by BrokerFk and only shows the Top 5 Commissions (GrossComm)
for that Broker. Any help? When I run the query, I keep getting a report
that only lists the Top 5 values for the whole database.

I've also tried:
1) creating a query that only list the top 5 values in the whole database and
then putting that query as a subreport in a report grouped on BrokerFk but
the same result happens. not sure what I am doing wrong?

 

[Duane Hookom]

I would make sure you have sorting and grouping levels on Broker (with
header) and then on GrossComm (no header/footer) . Then add a text box to the
detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code to the On Format section of the detail section:
Cancel = Me.txtCount>5