Unique Index (1 Viewer)

[georgedwilkinson]

I've run into an oddity using a unique index.

First let me review the rule. Let's assume a simple thing like a person's name:
ID - Surrogate Autonumber key
FName - Text
MName - Text
LName - Text

Just for argument's sake (not because I believe it), you should not be able to store information on 2 "records" with the same first and last name assuming you don't know the middle name. So, once you have "Fred Freeloader", you should not be able to input another record with that first and last name.

However, you should be able to enter "Fred F Freeloader" after entering "Fred Freeloader".

So, how do you design a Unique Index to accomodate these rules?

I bring this up because I "thought" I created a correct and proper UK in Access 2010 but the users have been able to enter up to 7 duplicates (only limited to 7 because they didn't try to enter 8). I have fiddled around with the "Ignore Nulls" property of the index but it doesn't seem to change the functionality of the index.

I have attached a dummy database that illustrates this odd behavior. You'll notice that I created 2 tables: Customer and Employee. They are identical except the value of the "Ignore Nulls" field on the UK.

To duplicate the problem I am seeing, enter the following records into either of the tables:
Fname - <leave blank/NULL> - Lname (Should work fine)
Fname - <leave blank/NULL> - Lname (Should fail but doesn't)
Fname - Mname - Lname (Should work fine since the Middle Name is not null like in the previous record)
Fname - Mname - Lname (Should fail and has consistently worked correctly for me)

Obviously, the UK is ignoring records that have a null in one of the fields. This is not the behavior I expected.

How do I create a Unique Index in Access 2010 that behaves the way a Unique Index is supposed to behave?

Please, don't get into whether this is the right approach to this particular problem. This is a simple illustration of a bigger problem I have with properly designed and normalized, object oriented tables.

I really appreciate any help on this.

Thanks, George

 

[Kiwiman]

Hi George

This is how it works in 2003 as well. Try setting the default value for MName to "".

 

[Kiwiman]

Howzit

See this for a discussion on the problem and where I got the potential solution.

 

[georgedwilkinson]

Drag. Sounds like it's broke and nobody wants to acknowledge it.

I really appreciate you guy's responses. I'll be able to work around the bug with your help!

Thanks!

 

[RossWindows]

For indexes that are reliant on more than one field, I sometimes create a SuperKey. It is simply an Indexed (No Duplicates) field with a text datatype.

when new records are created, my INSERT query inserts a calculated value into that field. i.e. given a table with the following fields: [ID] (Autonum, pk), [SK] (Indexed - No Duplicates), [FName], [MName], [LName]

The insert query would look something like this;

INSERT INTO tblThisTable ( SK, FName, MName, LName )
SELECT "John" & "_" & "Doe", "John", "Keith", "Doe";

FName and LName are concatenated with an underscore in between and then used as a whole in the SK field. This would prevent another John Doe from being added to the DB while ignoring MName.

I don't know if this is considered good or bad practice, but I use it sparingly.