Expert: Scottgem - 2/11/2005

Question
I have an Access 97 database.  In one of the tables, I would like to do 2 things:
1. For a specific field (name), I would like to get a list of all the unique names in that table (one person might have several rows in the table)
2. For each unique name, I would like to get a count of how many times that name appears in the table.

Thanks very much for your help!

Answer
Very easy to do and it can be done in one step with one query.

Create a query with 2 columns, the name field and the primary key field (in that order). Then turn the query into a Group By query (press the Sigma icon on the tool bar). Set the name field to Group By and the Primary key field to Count.

The result will be a record for each name and a count of the number of records for that name.

May I add, though, that the question indicates you may have design issues with your database. I would never have a table where a name is repeated multiple times. If I had a situation where I need to repeat like that, I would create multiple tables. For example, lets say your situation is such that you have people who join multiple clubs within an oprganization. In that case, you would have three tables a people table, a clubs table and a join table to assign people to clubs. The Join table would look like this:

tblPeopleClubs
PeopleClubID (Primary Key Autonumber)
PeopleID (Foreign Key to People table)
ClubID (FK to Clubs table)

You wouldn't have the name in the join table since you can get it by joining back to the People table.

HTH
Scott<>