Composite unique key with some fields that are null

[SherryScrapDog]

Hi,
I have a database full of names (for geneology) where I have Last, First,
Middle and Title. My table has an auto-number for primary key. From reading
posts here, I figured out how to make a composite unique key with the 4
fields. However, it is routine that some of the fields are null, especially
(but not limited to) the middle and title fields. I load this table from
muliptle imported Excel tables. I could have Doe, Jane in one record, Doe,
Jane, Mary in another, and Doe, Jane, Mary, Mrs. in a 3rd record. The
problem I am having is that it is loading all records even when they are the
same. For example, if I have Doe, Jane, null, null in 2 records, the second
record loads. I tried both options on the 'Ignore Null' on the Index. I
can't require the fields because they are many times properly null. Am I
missing something simple here? Do I need something specific in my append
query to prevent loading the duplicates? Thanks in advance if you can help!

 

[Allen Browne]

You cannot have a null in a primary key field.

If you have an AutoNumber for primary key, that's not a problem. You can
have nulls in the other fields.

You can make a composite index on the fields that contain nulls, but I don't
think making it a unique index is a good idea. Having 2 people with the same
name is not uncommon - particularly in geneologies where children are
sometimes named after their parents. It might be better to just give a
warning if the name is not unique rather than to block it via a unique
index. To give the warning, use the BeforeUpdate event procedure of the form
where you enter people.

If you do need to create a unique index where some fields are known to have
no value (e.g. you know the person actually has no middle name), you can use
a zero-length string (zls) instead of a Null. In theory, a Null means
unknown/not applicable/undefined, whereas a zls means the value is known not
to exist. So if you don't know someone's phone number, that's stored as a
Null, whereas if you know someone has no phone, that's a zls. You enter a
zls by opening and closing quotes with nothing between them.

In practice, there is no visible difference between a Null and a zls, so it
tends to really confuse any non-technical people who use the database.
Addionally, you must remember to handle both cases whenever you design
queries, filters, reports, search forms, and so on.

If you want to create the unique composite index on the 4 fields and use
zero-length stings instead of nulls, open the table in design view and set
these properties for each of the Text type fields you have in the index:
Allow Zero Length Yes
Default Value ""

Again, I don't really see how this will be useful for your case. To me it
does not seem logical to make a unique index such that there can be only
exactly one Jane-Doe-with-no-other-name. In most tables, I personally think
you want to block the possibility of zero-length strings in your text
fields:
http://allenbrowne.com/bug-09.html

Hope that's useful.

 

[SherryScrapDog]

Hi Allen and thank you for your response! Yes, there can be more than one
person with the same name, and it is another table I have that gives the
researcher the detail to a name that will let them decide if this is the
person they want. For example, we might have the name Jane Doe in 15
different places in our records and it might be the same Jane Doe, or it
could be different Jane Does. The detail records (such as the year and other
potential info) connected to the name Jane Doe will let them know. The
detail records are related to my names table by the auto-number primary key.
I did as you suggested with the Allow Zero Length and the default of "". My
append query, which is:
INSERT INTO Master1 ( [Last], [First], Middle, Title )
SELECT [Lastname], [Firstname], [Middle], [Title]
FROM ExcelData1;
is still loading all of the records. Please let me know if there is
anything else you can see that I am doing wrong. It does prevent the
duplicate if all 4 fields have a value.
thanks again, Sherry