Compound Index not Preventing Dup's (?)

[tbl]

In one of the tables in a db, I've tried to prevent
duplicate records by creating a compound index--but it's not
working as I hoped.

The table contains a foreign key for a "parent table", two
more foreign keys for lookup type tables, and three plain
data fields.

The index I made consists of all those fields except for the
last plain data field. The index is set to Primary-No,
Unique-Yes, and Ignore Nulls-No

But I can enter the same data in all those fields without
the index stopping it.

Something tells me I've missed a key concept here...

 

[Jeff Boyce]

Describe how you set up that multi-field index...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tbl]

Describe how you set up that multi-field index...

In the table's design review, menu items View|Indexes, I
cleared all existing indexes, then started a new row,
assigning the Index Name as "NoDups". On that same row, I
picked the first field to use in the index, and left the
sorting at the default of "Ascending".

On the subsequent rows, the Index Name column was left
blank, and the fields for the rest of the index were picked,
leaving the sorting at "Acsending".

With the first row of the index selected, I changed the
Unique property to "Yes". The other two properties were
left at their defaults ("No").

Is that what you were asking?

I should have mentioned in my previous message that one of
the fields in this index must be able to have a null value.
This is apparently what keeps me from using a Compound
Primary for this index.

 

[Jeff Boyce]

I would suspect the use of a field that allows nulls.

If you have two records that appear to be the same, but they both contain a
null in that field, are they the same or different? (not a rhetorical
question ... nulls mean "I have no idea", so you can't do simple comparison
of one null to another.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tbl]

I would suspect the use of a field that allows nulls.

If you have two records that appear to be the same, but they both contain a
null in that field, are they the same or different? (not a rhetorical
question ... nulls mean "I have no idea", so you can't do simple comparison
of one null to another.)

Ah. I was assuming that nulls would be treated such that
null would be like, well, data (I'm really having a hard
time describing this), in other words (in my mind...), you
could have two records with all the same data, *except* one
would be null for field "x", and one would have a real value
in field "x". But it sounds like that's the point where my
mind fell off the reality train.


This situation has caused me considerable experimentation.
As I mentioned at the start of this thread, two of the
fields are foreign keys for "lookup" tables. What I
*didn't* mention is that those tables are in a one-to-many
relationship with each other. They are for taxonomy
purposes: one is for Order, and one is for Family. I wanted
the forms where they are used to have cascading combo boxes,
so that selecting a given Order would limit which Families
were listed in the second combo box. But... sometimes the
scientist doesn't identify the Family. If the index allows
leaving null, then it doesn't work as a "no-dups" index. If
I put an entry in the Family table for "not identified", I
have to have an entry like that for each Order (ugh!).
That's how I've left it for now.

If any of this make sense to anyone else, and better ideas
come to mind, I'd love to hear 'em! It's not a
show-stopper, but it would be nice to get a little closer to
elegance.