Duplicates

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

 

[John Spencer (MVP)]

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.