Help counting occurences of duplicates.

[rhaazy]

I have a table with some information, the information includes a
persons first name and last name.

My problem is that there are duplicate entries of people in my table.

I have a query that will return all the records without any
duplicates, however I want to add to it the number of duplicates.

[ID] [First_Name] [Last_Name]
1 ryan smith
2 sue Johnson
3 ryan smith
4 doug anger
5 eric england
6 sue johnson

SELECT First([Counseling Meeting Data].ID) AS FirstOfID, [Counseling
Meeting Data].First_Name, [Counseling Meeting Data].Last_Name
FROM [Counseling Meeting Data]
GROUP BY [Counseling Meeting Data].First_Name, [Counseling Meeting
Data].Last_Name;

This returns records without duplicates, however I need something like
this.
[ID] [First_Name] [Last_Name] [Dups]
1 ryan smith 2
2 sue Johnson 2
4 doug anger 1
5 eric england 1

How do I change my existing query to add this column that keeps a
total of the number of occurences???

 

[John Spencer]

Try the following

SELECT First([Counseling Meeting Data].ID) AS FirstOfID
, [Counseling Meeting Data].First_Name
, [Counseling Meeting Data].Last_Name
, Count(ID) as DupCount
FROM [Counseling Meeting Data]
GROUP BY [Counseling Meeting Data].First_Name
, [Counseling Meeting Data].Last_Name;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

SELECT First(ID) as FirstOfID, First_Name, Last_Name, Count(ID) as Dups
FROM [Counseling Meeting Data]
Group BY First_Name, Last_Name

HTH
Dale