duplicates query

[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.

 

[John Spencer]

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
'====================================================

 

[John W. Vinson]

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.

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