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

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)

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 (AT) hotmail (DOT) com> wrote:

Thank you guys.
I very much appreciate your help.