Creating A Total Of Duplicate Entries

[JohnBoy2005]

I've got a table that contains many duplicate "names" fields.

E.g.

Name Date Type
Smith 10/10 School
Smith 12/10 Office
Jones 12/10 Council
Jones 13/10 School

I'm trying to design a query that would tell me how many "Smiths" are there or how many "Schools" are there.

I'm getting close but just not there yet.

John

 

[PHV]

Something like this (SQL code) ?
SELECT 'Name' AS DupField, Name AS DupValue, Count(*) AS DupCount FROM theTable GROUP BY Name HAVING Count(*) > 1
UNION ALL SELECT 'Type', Type, Count(*) AS DupCount FROM theTable GROUP BY Type HAVING Count(*) > 1

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