exclude records from a find duplicates query

[carpstar]

Hi to all that may see this post, it's been almost 18 months since I last posted here.

I have a find duplicates query (created using the wizard) that matches duplicates by LastName and FirstName.

SELECT [dup donors no zero zip].LastName, [dup donors no zero zip].FirstName, [dup donors no zero zip].DID, [dup donors no zero zip].Title, [dup donors no zero zip].Init, [dup donors no zero zip].Sex, [dup donors no zero zip].SSN, [dup donors no zero zip].DOB, [dup donors no zero zip].Address, [dup donors no zero zip].City, [dup donors no zero zip].State, [dup donors no zero zip].Zip
FROM [dup donors no zero zip]
WHERE ((([dup donors no zero zip].LastName) In (SELECT [LastName] FROM [dup donors no zero zip] As Tmp GROUP BY [LastName],[FirstName] HAVING Count(*)>1 And [FirstName] = [dup donors no zero zip].[FirstName])))
ORDER BY [dup donors no zero zip].LastName, [dup donors no zero zip].FirstName;

If all the records in a group of matched duplicates have a valid SSN then I need to exclude those records.

A valid SSN is ***-**-1234 where 1234 is any number.

 

[Rob.Mills]

Give this Sql a try:

SELECT [dup donors no zero zip].LastName, [dup donors no zero zip].FirstName, [dup donors no zero zip].DID, [dup donors no zero zip].Title, [dup donors no zero zip].Init, [dup donors no zero zip].Sex, [dup donors no zero zip].SSN, [dup donors no zero zip].DOB, [dup donors no zero zip].Address, [dup donors no zero zip].City, [dup donors no zero zip].State, [dup donors no zero zip].Zip
FROM [dup donors no zero zip]
WHERE ((([dup donors no zero zip].LastName) In (SELECT [LastName] FROM [dup donors no zero zip] As Tmp GROUP BY [LastName],[FirstName] HAVING Count(*)>1 And [FirstName] = [dup donors no zero zip].[FirstName])) AND (([SSN]) Not Like '###-##-####'))
ORDER BY [dup donors no zero zip].LastName, [dup donors no zero zip].FirstName;

 

[carpstar]

Rob,
No that didn't do it, it returned the same data. The query is returning data in grouped duplicates as follows.

example 1
FirstName
John
John
John
LastName
Smith
Smith
Smith
SSN
***-**-1234 valid ssn
***-**-2345 valid ssn
***-**-**** no ssn

example 2
FirstName
John
John
John

LastName
Smith
Smith
Smith
SSN
***-**-1234
***-**-2345
***-**-3456

In example 1 when there is any combination of valid and no ssn, or all records have no ssn we want to see these grouped records.

In example 2 when all gouped records have valid ssn we do not want to see any of the records in that group.

Hope this makes sense, and is possible by modifying the find duplicates query.
Any help or suggestions is very much appreciated