[AccessD] Unique index... NOT!!!

Guys,

I hand keyed the records myself.  The reason it is record 5
is that I deleted record 3 and 4 that also gave the exact
same result because I could not believe it let me do it.

I have been doing this since 1.0, so I am not a novice at
it.

Also, I think you will find that Access will trim the columns
before it saves them.  So, a space on the end would not matter.

Please real answers after you try this for yourself.

And, where does it say that two people cannot have the same
name, the business rules that govern the database in this
case. I am just working on a problem that one of the people
in my user group presented to me. I told him a unique index
would keep duplicate entries from being entered. Well, guess
what, it does not.

Here is the data using the ctrl-" so there is no possibility
of me entering it wrong:

tblName
NameID FName MName LName   Suffix
1      Jay   Ray   Johnson Jr
2      Jay         Johnson
5      Jay         Johnson
6      Jack  J.    Jones   I
7      Jack  J.    Jones
8      Jack  J.    Jones
9      Jack  J.    Jones

The unique index on all 4 columns stopped me from entering
Jack J. Jones I 2 times, but not Jack J. Jones 3 times when
I left the suffix empty.  It is not enforcing the unique
index unless all indexed columns have data.

Robert

At 02:29 PM 3/22/2007, you wrote:
>Date: Thu, 22 Mar 2007 13:45:55 -0500
>From: "Heenan, Lambert" <Lambert.Heenan at AIG.com>
>Subject: Re: [AccessD] Unique index...  NOT!!!
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <34C8A2AB1EF3564CB0D64DB6AFFDD5C204043F05 at xlivmbx35.aig.com>
>Content-Type: text/plain
>
>I suspect that either Mname or Suffix in one record holds a null value while
>in the other records it is an empty string "", which is not the same thing
>as Null. It could also be that one record has the first name as "Jay" and
>the other has "Jay ". The space on the end being the tiebreaker.
>
>However, why are you imposing this unique index anyway? Where does it say
>that two people cannot have the same name?
>
>Lambert
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart
>Sent: Thursday, March 22, 2007 2:09 PM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Unique index... NOT!!!
>
>
>      Access 2003
>      tblName
>      NameID FName MName LName   Suffix
>      1      Jay    Ray  Johnson   Jr
>      2      Jay         Johnson
>      5      Jay         Johnson
>      Name ID is the primary key.
>      There is a unique index on FName, MName, LName, and Suffix.
>      Why is it allowing the entry of Name ID # 5?
>      It is not enforcing the unique index.
>