Multi-field primary key, no dupes - but only if one field is empty?

[Ed from AZ]

I can select multiple fields and make a primary key indexed with no
duplicates. But I just realized my criteria for "no dupes" holds only
if another field is empty (no value).

For example, if my primary key is:
Shirts-Mens-Style102-Large-Green-05Jan08
then I can't enter another order for the same product on the same
day.

That's good - unless the order's been shipped and another one is
needed. So no duplicates, but only as long as the ShipDate field for
that record is empty.

Can this be done easily? Or should I look into a different way to
prevent duplicates?

Ed

 

[Jeff Boyce]

Using the combination of all those fields as a primary key may be more work
than you need to do.

Using the combination of all those fields as a unique index might accomplish
what you want (you'd still need a primary key, but that's a different
matter).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ed from AZ]

Thanks for responding, Jeff.

How can I set this up as a unique index as:
"all these fields and this other field ONLY if this other field is
empty/null"??

Ed

 

[Jeff Boyce]

I'm not sure I fully understand the need for the exclusion. If the
combination of fields is unique, and if there's nothing in one of those,
that's unique, right? And if you have another, with a value in that field,
then that combination is also unique, right?

What am I missing here?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for responding, Jeff.

How can I set this up as a unique index as:
"all these fields and this other field ONLY if this other field is
empty/null"??

Ed

 

[Ed from AZ]

What am I missing here?
Jeff, I'm sure _I'm_ the one who's missing something.

But I think I get it now.

I was thihnking that an index had to be made up of non-changing data.
Once I entered all the info and it went into the table, the index was
created and that was it. I didn't think about simply including a
filed into the index that was empty now and then filled in.

As long as the last field in empty, an attempt to enter a duplicate is
invalid. As soon as I fill in that last field, the index has
changed. Now an entry duplicate in every field except the last one
(empty until filled in later), doesn't violate the uniqueness.

Thank you!!!
Ed

 

[Ed from AZ]

Okay - not quite as understanding as I thought I was.

I had the index made up of six fields. The sixth field is the empty
date field. In the Indexes window, they are arrainged in the order I
want them sequenced. Only the first field has the Index Name; the
others are blank in the Name field of the Index window.

At first, this was the primary key. But I got an error that the
Primary Key could not contain null values. So I removed the Primary
Key designation and inserted an autonumber field as the Primary.

Then I went into the table and entered a duplilcate record - and it
was accepted with no error! Every field of both records contained the
exact same info (except the autonumber, which is not a part of this
index).

Where do I go now?

Ed

 

[Jeff Boyce]

When you set up that 6-column index, did you also set the "Unique" property
for it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay - not quite as understanding as I thought I was.

I had the index made up of six fields. The sixth field is the empty
date field. In the Indexes window, they are arrainged in the order I
want them sequenced. Only the first field has the Index Name; the
others are blank in the Name field of the Index window.

At first, this was the primary key. But I got an error that the
Primary Key could not contain null values. So I removed the Primary
Key designation and inserted an autonumber field as the Primary.

Then I went into the table and entered a duplilcate record - and it
was accepted with no error! Every field of both records contained the
exact same info (except the autonumber, which is not a part of this
index).

Where do I go now?

Ed

 

[Ed from AZ]

Okay - I got it now. I set a default value to that field so it's not
null. But it's so obviously incorrect (1/1/1900) that of course it
would scream "change me!" when I need to enter a real date.

Just tested it, and could not enter a duplicate value. So I am now
ready to move on!

Thanks for all your help, Jeff.
Ed