count filter 1

[barfless]

If I have 10,000 record table with 2,000 total vendors and I want a report that shows the top 100 vendors that have been used the most, is there a vb statement, a count calculation or something that doesn't use a make table and autonumber that I can use to accomplish this.

Tbl = 10,000 vendor entries
Qry = 10,000 vendor entries

rpt:

vendor total = 2,000 (different vendors)

vendor #1 - count = 85 entries
vendor #2 - count = 75 entries
vendor #3 - count = 70 entries
vendor #4 - count = 35 entries
vendor #5 - count = 29 entries
etc. up to 2,ooo

REPORT:
The above information is nice but I want to see a list of vendors sorted by count, decending;(as above) and I want to be able to filter the 2,000 records to show only the top 50. Any solutions would be greatly appreciated. thanks, dm


If this is clear as mud, I can try some windex if needed.

 

[PHV]

Something like this (SQL code) ?
SELECT TOP 50 [Vendor field], Count(*) As CountOfEntries
FROM yourTable
GROUP BY [Vendor field]
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886