AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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.
