duplicates query

  • Thread starter Thread starter JP
  • Start date Start date
J

JP

I created a find duplicate records query with the wizard that works fine.
However, I need to return the non-duplicate records in a query. ie, if my
table has 12 records and 2 dupes, I need the query to return the 10 good
records, and not the two dupes as it is doing now.
 
Quick and easy: Change the criteria subquery's Having clause to return
records where the count is equal to one, instead of greater than 1.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I created a find duplicate records query with the wizard that works fine.
However, I need to return the non-duplicate records in a query. ie, if my
table has 12 records and 2 dupes, I need the query to return the 10 good
records, and not the two dupes as it is doing now.
Click to expand...

Your query should look something like this in SQL view:

SELECT Client.Address1, Client.Client
FROM Client
WHERE (((Client.Address1) In (SELECT [Address1] FROM [Client] As Tmp GROUP BY
[Address1] HAVING Count(*)>1 )))
ORDER BY Client.Address1;

obviously with different table and fieldnames. Just change the

HAVING Count(*)) > 1

to

HAVING Count(*)) = 1
 
Back
Top