Duplicates

  • Thread starter Thread starter Always Learning
  • Start date Start date
A

Always Learning

Hi Guys,

This is my first posting here.
Could you please help me with this.
I have a table full of customer names & address's. Each record also has a
unique customer number.
What I need to do is pull out all the records including the customer number
but I do not want records with a duplicate Surname, Address Line 1 &
Postcode
I have tried various ways but because I need the Customer Number to be
included, when ever I include this field all records are considered unique
because the Customer Number is unique but there are duplicate (Surname,
Address1, Postcode)
Hope this makes sense.

Best Regards,

Steve.
 
Easiest (and often the fastest in Access) is to use two queries.

First query
SELECT Surname, Address1, PostCode
FROM YourTable
GROUP BY Surname, Address1, PostCode
HAVING COUNT(CustomerNumber) > 1

Save that as QCount

Now use that in the second query

SELECT SurName, Address1, PostCode, CustomerNumber
FROM YourTable as A INNER JOIN QCount as Q
ON A.Surname = Q.Surname and
A.PostCode = Q.PostCode and
A.Address1 = Q.Address1

If you must use the query grid -
Build a group query on Surname, Address1, and Postcode and count the customer
numbers, Save that and then use that in a second query along with your table.
Join on the three fields that are in common between the table andthe query.
 
Back
Top