Indexing Problem

B

Ben Watts

I am having a bit of trouble here plus I am relatively new to this. I have
created table and 4 of the fields I use as one index to prevent duplicates.
So what I have done is imported my data from an excel sheet and then
reimported the exact same sheet to see if the index would work. It allowed
some of the records into the database. Why is it doing this?
 
J

John W. Vinson

I am having a bit of trouble here plus I am relatively new to this. I have
created table and 4 of the fields I use as one index to prevent duplicates.
So what I have done is imported my data from an excel sheet and then
reimported the exact same sheet to see if the index would work. It allowed
some of the records into the database. Why is it doing this?
Click to expand...

Probably because you made some sort of mistake... which we can't see from
here!

Please post the relevant fields of your table and the specifications of the
index, and some examples of the records that are getting in that you don't
want.

John W. Vinson [MVP]
 
B

Ben Watts

I figured it out, it was the data, not me. When I would re import the second
time it was bringing back all of the records that were blank in one of the
indexed fields. So I assume when I imported the first time it changed them
to a null value and then when I did it the second time it saw them coming in
as blank. Not sure but I fixed it!
 
J

John W. Vinson

I figured it out, it was the data, not me. When I would re import the second
time it was bringing back all of the records that were blank in one of the
indexed fields. So I assume when I imported the first time it changed them
to a null value and then when I did it the second time it saw them coming in
as blank. Not sure but I fixed it!
Click to expand...

Just note that a NULL value is not equal to anything - *even another NULL
value*. Two records which have four matching values in four of the fields, and
NULL in the fifth, will not violate the unique index.

There's an "Ignore Nulls" setting in the index properties which you might try
to see if it changes this behavior.

John W. Vinson [MVP]
 
Top