Posted by Roy Harvey on 07/23/07 13:34

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
<theintrepidfox@hotmail.com> 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

[Back to original message]