How do I count the top five most prevalent text entries in a table

[lance carter via AccessMonster.com]

So for instance a column of 200 company names. They are chosen by the user
from a listbox to be entered into the table.

Let's say one company X appears 20 times, company Z appears 13 times. I
would like to list them by top 5 ie:

1 Company x (20)
2 Company Z (13)
3 ...
4
5

I would ideally like to do this within the control data dialog box, as I'm
a little weak on 'Queries'. Otherwise I'm comfortable with VBA code if it
can be done programatically.

I'm many hours into googling, searching, reading access help and here..and
finally gave up. Any help very gratefully received.

 

[Guest]

Hi - I know this isn't your preferred answer but to do this using a query
take the following steps:

Create a new query in design view. When prompted select the table where your
information is stored. Click Add and then Close. In the query design view
drag the company name into the results panel twice.

Then click 'View' and select 'Totals'

In the results pane a row for Total will have appeared. Change this for the
second company name column from 'Group By' to 'Count'. Also change the sort
to Descending.

At the top of the screen there is a drop down box where you can select the
number of records you wish to be shown when the query is run.

All you need to do now is run the query.

Hope this helps
Ian

 

[lance carter via AccessMonster.com]

Ian,

Thank you so much for your help. your succinct instructions were faultless
and easy to understand. I've now created the query, created a new report
on that query, re-designed the report form and dumped it onto my own main
form..exactly where I wanted it. Cheers.