Removing duplicate records

[dawnd3]

I want to know if it is possible for Access to be smart enough to determine which duplicate to get rid of. i.e. if two records have the same email address, but one has no name and one does, we'd want to delete the one that does not. However, there are many records that are not duplicates that have no name, so I wouldn't want those to end up in the delete pile. There are other criteria I would want to test as well. Thanks, Dawn

 

[dhookom]

Do you have a primary key?
You may need to run some totals queries or use a subquery or make table query or a combination.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You can build a simple query to find the IDs of the records that have no name, but another record with the email exists

qryNullNameID

SELECT
A.ID AS NullNameID
FROM
tblOne AS A
INNER JOIN
tblOne AS B
ON
A.emailAdd = B.emailAdd
WHERE (((A.customerName) Is Null) AND (Not (B.customerName) Is Null));

Then you can run a delete query.
DELETE
tblOne.ID
FROM
tblOne
WHERE
(((tblOne.ID) In (Select NullNameID from qryNullNameID)));