About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Using MS Access - Access 97: how to extract unique values in a column
Expert: Geoff - 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 Hi Sheree,
You could use the find duplicates wizard to generate this, however the SQL will be along the lines of....
SELECT [Name], Count([Name]) AS NumOff
FROM tblYourTableName
GROUP BY [Name]
ORDER BY [Name];
If you wanted to further enhance this and only list those names with more that 1 appearance, then modify the query as follows:
SELECT [Name], Count([Name]) AS NumOff
FROM tblYourTableName
GROUP BY [Name]
HAVING Count([Name])>1
ORDER BY [Name];
