Avoid Dups

[SAC]

Access 2003

I'm attempting to append customer records to a table and avoid duplicates.

I made the table with a unique index consisting of:

First
Last
Address
Address1
City
State
Zip

After I import records from an existing mdb, I repeat it from the same mdb
and only 75 out of 844 are not appended.

The 75 records have ALL fields NOT Null. If one of the fields is null it'll
allow a dup.

The index shows Indexed Yes (No Duplicates).

It looks like when one of the fields is null, then it allows a dup.

When do I need to change.

Thanks for your help.

 

[Jerry Whittle]

Nulls are the big difference between a unique index and a primary key. If you
change that combination of fields to a primary key, that won't happen.

However that means all your existing data in those field must not have nulls
in it. Also Access won't allow you to import a record that has a null in it.
Often the second address field is often null so this could be a problem.

You may need to first import the data into another table then use an append
query that first checks if a record already matches an existing record. If it
does, don't append it. This would probably take a fancy subquery.

Then there is a problem if the customer moves. Same First, Last, and maybe
even City and Zip. How do you handle them?

With "only" about 800 records, it may be best to do it manually if this is a
one time thing.

 

[r lonergan]

Access 2003

I'm attempting to append customer records to a table and avoid duplicates.

I made the table with a unique index consisting of:

First
Last
Address
Address1
City
State
Zip

After I import records from an existing mdb, I repeat it from the same mdb
and only 75 out of 844 are not appended.

The 75 records have ALL fields NOT Null. If one of the fields is null
it'll allow a dup.

The index shows Indexed Yes (No Duplicates).

It looks like when one of the fields is null, then it allows a dup.

When do I need to change.

Thanks for your help.