Index on 4 fields; one is optional

#11

"paii, Ron" <none (AT) no (DOT) com> wrote in
news:i53qnk$kpl$1 (AT) news (DOT) eternal-september.org:

Quote:

A composit key that is NOT the primary key can have NULL values in
the fields. But because NULL <> NULL, all the other fields in the
index can be equal without failing the index.Which is why NULL is
not allowed in a primary key.

The point is that you can't enforce real uniqueness if you allow
Nulls in any of the fields in the composite key or index.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#12

On Aug 25, 4:38*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Quote:

"bobh" <vulca... (AT) yahoo (DOT) com> wrote in message

news:f9f08e69-440c-4ff7-8220-ea8f865dfb1d (AT) j8g2000yqd (DOT) googlegroups.com...
On Aug 23, 4:07 pm, XPS35 <xps... (AT) gmail (DOT) com> wrote:

First a few words on dutch names.

In dutch a surname can consist of two parts: a (optional) prfix plus a
surname. So "Piet van der Graaf" is a typical dutch name. In databases
dutch surnames are stored in two fields.

In my databes I have 4 fields which I want to be a ubique index:

- First name (Piet)

- Prefix to surname (van der)

- Surname (Graaf)

- Date of birth

I created a index (unique, not primary, ignore nulls). If all fields are
not null, everything works as hoped. Levaing the prefix null gives
unwanted results. If I enter 2 records (Jan,null , Punt, 01/01/1960)
Access aacepts both.

What can I do to create a unique index with an optional field?

TIA.

--
Groeten,

Peterhttp://access.xps350.com

--- news://freenews.netfront.net/ - complaints: n... (AT) netfront (DOT) net ---
Many times I have a field in my table generally called 'UKey' and when
I'm saving a record I create a unique key with this instead of trying
to create a primary key using other table fields

MyRec!UKey = Format(Date, "mmddyyyy") & Format(Time(), "hhnnss")

bobh.

Seconds will not guarantee a unique value. Why not use a autonumber?- Hide quoted text -

- Show quoted text -

but using a 4 digit year with it does ex: 09252010091324 can never
be duplicated using this
MyRec!UKey = Format(Date, "mmddyyyy") & Format(Time(), "hhnnss")

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Index on 4 fields; one is optional

comp.databases.ms-access comp.databases.ms-access

Re: Index on 4 fields; one is optional - 08-26-2010 , 02:16 PM

Re: Index on 4 fields; one is optional - 10-01-2010 , 01:11 PM