Eliminating duplicates

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

Guest

I find that I can eliminate duplicates in a table, but I want to be able to tell Access which records to keep as the remaining duplicate. For example, I have a Field called Cust Number that has many duplicates that I want to wittle down so that a customer is not listed twice. But I want the customers remaining to be associated with another Field with a specific value (number) in it. I want it to keep all customers that also have a value of "3" in another field in the record.
 
What about if you have duplicates, and none of the records for the customer
have a value of 3?

If you don't want any of those, you could try creating a subquery to
determine all of the duplicate Cust Number, along the lines of:

SELECT [Cust Number]
FROM MyTable
GROUP BY [Cust Number]
HAVING Count([Cust Number]) > 1

Save that query (let's call it qryDuplicates to simplify the discussion),
then create a second query along the lines of:

DELETE FROM MyTable
WHERE [Cust Number] IN (SELECT [Cust Number] FROM qryDuplicates)
AND TheOtherField <> 3


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bc said:
I find that I can eliminate duplicates in a table, but I want to be able
Click to expand...
to tell Access which records to keep as the remaining duplicate. For
example, I have a Field called Cust Number that has many duplicates that I
want to wittle down so that a customer is not listed twice. But I want the
customers remaining to be associated with another Field with a specific
value (number) in it. I want it to keep all customers that also have a
value of "3" in another field in the record.
 
Good question. If none of the records have a field with a value of 3, I would prefer that they not be listed. Maybe the best way to take care of this would be in another query?
 
I tried that and it worked. However, I need to take it one step further as I found out there are several cust numbers that have a 3 in another field. What statement would I use to tell it to list a duplicate only once, even if it has more than one 3

Example

Cust number Cod
5
5
5
5
5
5
I want to eliminate the duplicate 5's, and keep only one. The one to keep must be a cust number that has a code of 3.
 
Are all of the fields duplicate, or are there differences in other fields?

If all of the fields are duplicate, create a query that uses the DISTINCT
keyword and it will filter out the duplicates.

If there are differences, then you'll have to come up with some other
criteria to decide which to keep and which to delete.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


bc said:
I tried that and it worked. However, I need to take it one step further
Click to expand...
as I found out there are several cust numbers that have a 3 in another
field. What statement would I use to tell it to list a duplicate only once,
even if it has more than one 3?
Example:

Cust number Code
5 3
5 3
5 3
5 3
5 4
5 1
I want to eliminate the duplicate 5's, and keep only one. The one to keep
Click to expand...
must be a cust number that has a code of 3.
 
Back
Top