• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

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

0
jegajothy
Asked:
jegajothy
  • 3
  • 2
  • 2
  • +1
1 Solution
 
peter57rCommented:
SELECT Distinct [Seniors Club].Email, [Seniors Club].[Last Name]     .......
0
 
tigin44Commented:
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

0
 
jegajothyretiredAuthor Commented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
peter57rCommented:
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) .  
0
 
als315Commented:
If you like to select unique Email only, you can group by email and select first of Last Name:
SELECT [Seniors Club].Email, First([Seniors Club].[Last Name]) AS LastName
FROM [Seniors Club]
GROUP BY [Seniors Club].Email;
0
 
als315Commented:
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?
0
 
als315Commented:
You also may need to prepare email field for compare:
trim(Replace(Replace(Nz([Seniors Club]![Email],""),"<",""),">",""))
0
 
jegajothyretiredAuthor Commented:
your syntax produced the right and expected results. thank u
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now