asked on

Access 2007 - duplicates

My OS is windows 7, I have the following SQL statement which pulls out all the duplicates. How do I modify the code to only extract the unique records. thank u

SELECT [Seniors Club].Email, [Seniors Club].[Last Name]
FROM [Seniors Club]
WHERE ((([Seniors Club].Email) In (SELECT [Email] FROM [Seniors Club] As Tmp GROUP BY [Email] HAVING Count(*)>1 )))
ORDER BY [Seniors Club].Email;

Open in new window

peter57r

SELECT Distinct [Seniors Club].Email, [Seniors Club].[Last Name] .......

tigin44

this wil only list the unique records...

SELECT [Seniors Club].Email, [Seniors Club].[Last Name]
FROM [Seniors Club]
WHERE [Seniors Club].Email NOT IN (SELECT [Email] 
										FROM [Seniors Club] As Tmp 
										GROUP BY [Email] 
										HAVING Count(*)>1 )
ORDER BY [Seniors Club].Email;

Open in new window

Jegajothy vythilingam

ASKER

in response to Peter57r, I ran your code, but the results are confusing.
There are 112 records. a straight query for duplicates reveals 7 duplicates. But your code reveals 110 unit records. I have Trimed the data inthe email field to eliminate the blank or leading spaces.
Hope u can throw some light. thank u
In response to Tigin44, your code did not produce any results, which means no Unique records.
Hope u can troubleshoot the code. thank u.

peter57r

What is your duplicates test based on in your duplicates query?

Your question stated you wanted unique records, and that is what Distinct will produce, based on the value of BOTH selected fields (Email + LastName) .

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

als315

If you are working with db from Question 26834411, you task is not so simple.
As I can see, one person may have different emails:
bavaniraj@verizon.net      V N Rajgopalan
bavaniraj@yahoo.com      Virinchipuram Rajgopalan
And different persons can have one email:
narendra03@aol.com      Chand Arora
narendra03@aol.com      Narendra Arora

What results are you expecting to get in this cases?

als315

You also may need to prepare email field for compare:
trim(Replace(Replace(Nz([Seniors Club]![Email],""),"<",""),">",""))

Jegajothy vythilingam

ASKER

your syntax produced the right and expected results. thank u