Create a top 10 list

[automaticbaby]

I have a list of users and the sum of their transactions. Is there a way to only get the top 10 with the most transactions in a report and not print out the rest? On top of that, the report is split up by location, which the report is grouped by. So, I need a top 10 from each location on 1 report.

At this point, I've created a report separated by location and in descending order by transactions, but I can't suppress anything after the 10th user.

 

[JoanneM]

You have 3 steps to do this

1 set topValues to 10
2 select Max values from transaction field
3 sort descending


1 Bring the properties list up by
pressing the button with a finger pointing to paper on
your tool bar

or
put your cursor in the grey area of the query grid
right click
a submenu will have properties at the bottom

in the properties list select
Top Values put 10 in here
This will return the first 10 values in your data set

2 Next you have to get greatest value
this can be done by pressing the SUM button on the
tool bar at the top of the page - the one like a
reversed 3

You will see a TOTALs line appear beneath the tablname
line.

In your transaction column select MAX

This will pull the maximum values for this field

3 Next sort it Descending

This should give you the 10 Maximum values in descending order

hope this is what you want

jo

 

[dhookom]

I would add a text box to the detail section of the report:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Then use code in the On Format event of the detail section:
Cancel = Me.txtCount > 10

This could also be accomplished by using a subquery in your report's record source.

Duane
MS Access MVP

 

[automaticbaby]

Thanks dhookom. That did the trick.