Duplicates

[rob77]

I need to find Duplicates based on more than 1 column and i'm finding the MSAccess Wizard isn't giving me the correct info.

EXAMPLE: I want to find duplicates where SNAME, FNAME and DOB match

Access gives me....
ID SNAME FNAME DOB BIRTH
01 JONES JOHN 01/01/01 UK
02 JONES JOHN 01/01/01 AUS
03 JONES JOAN 31/01/96 UK

but i would only expect to see the first 2 rows of data as SNAME, FNAME and DOB match exactly.

Any Ideas?

 

[PHV]

SELECT SNAME, FNAME, DOB
FROM yourTable
GROUP BY SNAME, FNAME, DOB
HAVING Count(*)>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rob77]

It's not quite what i wanted, but thanks for your help.

I've got my
SELECT Fname, Sname, DOB, Birth etc
FROM my table
WHERE (((

.[FORENAME]) In (SELECT [FORENAME] FROM

As Tmp GROUP BY [FORENAME],[SURNAME],[DOB] HAVING Count(*)>1 And [SURNAME] =

.[SURNAME] And [DOB] =

.[DOB])))
ORDER BY fname etc

I think it's somewhere in this Where statement that is bringing through the fornames that match up but showing all of the surnames

e.g.

John Jones
John Jones
John James

As i'm not doing a like match i don't want the john james to appear.

 

[PHV]

You wanted this ?
SELECT A.ID, A.Fname, A.Sname, A.DOB, A.Birth
FROM yourTable AS A INNER JOIN (
SELECT Fname, Sname, DOB FROM yourTable
GROUP BY Fname, Sname, DOB HAVING Count(*)>1
) AS B ON A.Fname=B.Fname AND A.Sname=B.Sname AND A.DOB=B.DOB

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886