The Top 25

[Guest]

I have a query where I have counted events that have occurred in different
cities around the country and sorted it by the highest number. So my list
looks something like this.

New York, NY 25
La Jolla, CA 21
Los Angeles, CA 13.......etc

I have approx 100 records in my query (so in other words, we have had events
in 100 different cities), but I want to only keep the top 25. Any ideas how
to create a list (in a report) that only shows the top 25? Thanks for your
help!

Susie

 

[Douglas J. Steele]

Create a new query based on your existing query. Select the city and count
fields, order it by count, then go to the query's Properties. You should see
one property labelled "Top Values". Change it from All to 25.

Your SQL will look something like:

SELECT TOP 25 City, Count
FROM MyQuery
ORDER BY Count

Note that you're actually not guaranteed to get 25 rows back: if there's a
tie for the 25th position, you'll get as many cities as has that count.