Removing Duplicate Records

[Michael J. Strickland]

I've written some code to De-dupe a table of addresses (Source).

I want to remove all records with identical FIRST, LAST, ADDRESS, and ZIP
fields.

Therefore, I created a new (blank) table with an index consisting of the
above 4 fields and set:
.Unique = True
.IgnoreNulls = False
.Required = False

I then copy the original (Source) table into this blank, indexed table.

The following two records illustrate the problem:

ID FIRST LAST ADDRESS ZIP
1 Null "Smith" "123 Main St" "12345"
2 Null "Smith" "123 Main St" "12345"

I consider the above two records identical, however since the Null values
for FIRST are not considered identical, ACCESS will not create identical
indexes for these two records and therefore it won't prohibit the second
(duplicate) record from being appended.

--

 

[John Nurick]

Hi Michael,

I think you'll need to use a second stage of de-duping. As you say,
records where FIRST is null and LAST, ADDRESS and ZIP match are not the
same as records where FIRST, LAST, ADDRESS and ZIP all match.