Finding Dupes across multiple fields

[MichaelaLee]

Hi Everyone,
I have a need to find dupes across multiple fields in a few tables and the queries I've used so far are not working for me. Here are exapmle of fields in the table:
PatientID
AdmissionDate
Name
Relationship
Age
How would I go about finding dupes across the first 4 fields. Thanks for any help.
Michael

 

[SQLSister]

So by dups you mean that all four fields have exactly the same information? Try:

Select PatientID, AdmissionDate, Name, Relationship, Count(*) from MyTable group by PatientID, AdmissionDate, Name, Relationship Having Count(*) > 1

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[MichaelaLee]

Thank you SQLSister for your reply. That worked great. Now I have one more request. How do you change it so that it will delete the dupes without deleting all the records. Meaning, I would like to delete all the dupes but one.
I had the following but it deleted all the dupes:

DELETE PatientID AS [PatientID Field], AdmissionDate AS [AdmissionDate Field], Name AS [Name Field], Relationship AS [Relationship Field], PatientID AS NumberOfDups, PatientID, AdmissionDate, Name, Relationship, Relationship
FROM HouseHolds
WHERE (((PatientID)>"1") AND ((Relationship)>"1"));

Thanks for the help.
Michael