Find Duplicate query - help

[fluffyvampirekitten]

I want to filter all the duplicate Name , dob , gender and postal code but view all the fields


AND filter by c_source = "SAP"

Here is the code :

SELECT *
FROM CrossSystemData
WHERE (((CrossSystemData.NAME) In (SELECT [NAME] FROM [CrossSystemData] As Tmp GROUP BY [NAME],[DOB],[GENDER],[POSTAL_CODE] HAVING Count(*)>1  And [DOB] = [CrossSystemData].[DOB] And [GENDER] = [CrossSystemData].[GENDER] And [POSTAL_CODE] = [CrossSystemData].[POSTAL_CODE])))
ORDER BY CrossSystemData.NAME, CrossSystemData.DOB, CrossSystemData.GENDER, CrossSystemData.POSTAL_CODE;

Any idea on how to do this?
Thanks in advance

[ranman256]

Did you use the query wizard to create a Find Duplicate query with it?

[LaughingBull]

on the properties of the query select yes by duplicate Values and it will filter duplicates

[fluffyvampirekitten]

Did you use the query wizard to create a Find Duplicate query with it?

Yes , I used query wizard .

[fluffyvampirekitten]

on the properties of the query select yes by duplicate Values and it will filter duplicates

Select what Yes ? in the query properties ?

[LaughingBull]

Yes hope this helps

[fluffyvampirekitten]

Yes hope this helps

Thanks for the help , however it doesn't filter out the unique values .

here is the sql statement that was generated .
Is there anything wrong with it ?

SELECT DISTINCT *
FROM CrossSystemData
WHERE (((CrossSystemData.[NAME]) In (SELECT [NAME] FROM [CrossSystemData] As Tmp GROUP BY [NAME],[DOB],[GENDER],[POSTAL_CODE] HAVING Count(*)>1  And [DOB] = [CrossSystemData].[DOB] And [GENDER] = [CrossSystemData].[GENDER] And [POSTAL_CODE] = [CrossSystemData].[POSTAL_CODE]))) 
AND ((CrossSystemData.[C_SOURCE]) = ("SAP"))
ORDER BY CrossSystemData.[NAME], CrossSystemData.[DOB], CrossSystemData.[GENDER], CrossSystemData.[POSTAL_CODE];