how to display all duplicate records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble displaying all duplicate records that meet a certain
criterion. I want them all displayed in the query so I can pick and choose
which ones I want to delete. This is the SQL code that's not doing it; that
is, displaying only the first of the duplicate records:

SELECT Lead_ID, Possession, Contact_Street1, Contact_City1, Contact_ST1,
Contact_ZIP1, Year_Built, Price, DateCreated, Source
FROM Listings
Where Lead_ID is not null
GROUP BY Lead_ID, Possession, Contact_Street1, Contact_City1, Contact_ST1,
Contact_ZIP1, Year_Built, Price, DateCreated, Source, Data_Source, Area_Name,
Description_Ad
HAVING Count(Listings.Lead_ID)>1
ORDER BY Listings.Lead_ID
 
Actually that is not displaying the first duplicate at all, it is displaying
a combined copy of all the records that are an exact match for all the
fields.

Have you tried using the Find Duplicates query wizard to build the query you
want?

Which field(s) determines that a record is a duplicate? You can try the
following

SELECT Lead_ID, Possession, Contact_Street1, Contact_City1, Contact_ST1,
Contact_ZIP1, Year_Built, Price, DateCreated, Source
FROM Listings
Where Lead_ID IN

(SELECT Lead_ID
FROM Listings
WHERE Lead_ID is Not NULL
GROUP BY Lead_ID, Possession, Contact_Street1, Contact_City1,
Contact_ST1,
Contact_ZIP1, Year_Built, Price, DateCreated, Source, Data_Source,
Area_Name,
Description_Ad
HAVING Count(Listings.Lead_ID)>1)

ORDER BY Listings.Lead_ID, Possession

If some of the items in the list of data have no relevance in determining
whether or not a record is a duplicate, then remove them from the GROUP By
clause in the subquery.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks, John. What you suggested worked perfectly. I had tried the Find
Duplicates wizard in Access but found that it only brought the first record
up. I tried to tweak it, however, with what I showed in my first post, and
which obviously didn't work, so I'll have to compare and see how it differs
from the solution that you gave me. Thanks again.
 
Back
Top