Need help with finding duplicate records please

Dear Group

Please accept my apologies for this trivial question. I can't get it
to work.
What I'm trying to achieve is to find duplicate contact records. E.g.
There might be two records relating to the same individual. Being
enetered as Phil Baker and Philip Baker. For that purpose I do a
duplicate serach on the lastname and a comparison on the first two
characters of the firstname to get a list of potential duplicates.

Please fiond the script I tried below.

SELECT ContactID, Lastname, Firstname FROM Contacts
WHERE LEFT(Firstname,2) IN
(SELECT LEFT(Firstname,2) FROM Contacts GROUP BY HAVING COUNT(*)>1)
AND Lastname IN
(SELECT Lastname FROM Contacts List GROUP BY Lastname HAVING
COUNT(*)>1)
ORDER BY Lastname ASC

I'm grateful for any hints and suggestions.
Thank you very much for your time and efforts,

Martin

Re: Need help with finding duplicate records please

Lots of ways to do this, such as

SELECT c1.ContactID, c1.Lastname, c1.Firstname
FROM Contacts c1
WHERE EXISTS (SELECT * FROM Contacts c2
WHERE c1.Lastname=c2.Lastname
AND LEFT(c1.Firstname,2)=LEFT(c2.Firstname,2)
AND c1.ContactID<>c2.ContactID)

Re: Need help with finding duplicate records please

Try this approach.

SELECT ContactID, Lastname, Firstname
FROM Contacts as A
JOIN (SELECT LEFT(C.Firstname,2) as F1,
C.LastName
FROM Contacts as C
GROUP BY LEFT(C.Firstname,2), C.LastName
HAVING COUNT(*) > 1) as B
ON LEFT(A.Firstname,2) = B.F2
AND A.LastName = B.LastName

Roy Harvey
Beacon Falls, CT

On Mon, 23 Jul 2007 06:15:19 -0700, theintrepidfox
wrote:

>
>Dear Group
>
>Please accept my apologies for this trivial question. I can't get it
>to work.
>What I'm trying to achieve is to find duplicate contact records. E.g.
>There might be two records relating to the same individual. Being
>enetered as Phil Baker and Philip Baker. For that purpose I do a
>duplicate serach on the lastname and a comparison on the first two
>characters of the firstname to get a list of potential duplicates.
>
>Please fiond the script I tried below.
>
>SELECT ContactID, Lastname, Firstname FROM Contacts
>WHERE LEFT(Firstname,2) IN
>(SELECT LEFT(Firstname,2) FROM Contacts GROUP BY HAVING COUNT(*)>1)
>AND Lastname IN
>(SELECT Lastname FROM Contacts List GROUP BY Lastname HAVING
>COUNT(*)>1)
>ORDER BY Lastname ASC
>
>I'm grateful for any hints and suggestions.
>Thank you very much for your time and efforts,
>
>Martin

Re: Need help with finding duplicate records please

Thank you guys.
I very much appreciate your help.