Re: Compound Index not Preventing Dup's (?)

On Thu, 25 Jan 2007 12:18:44 -0800, "Jeff Boyce"
<nonsense@xxxxxxxxxxxx> wrote:

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.
--
tbl
.

Relevant Pages

  • Re: Unique index not recognizing null
    ... behind ignore nulls. ... Includes: Field1 and Field2 ... If Field2 is a Foreign Key, then if there is no entry in the primary table, ... Field: ID Autonumber, Primary Key ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Unique index not recognizing null
    ... behind ignore nulls. ... Includes: Field1 and Field2 ... If Field2 is a Foreign Key, then if there is no entry in the primary table, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: FAQ Topic - Internationalization and Multinationalization in javascript.
    ... Before I change the entry, are you going to change your mind again in 10 months or so? ... The FAQ workers are a group of volunteers. ...
    (comp.lang.javascript)
  • Re: Dervish - the worst band in Europe
    ... I believe it's called 'Post Modern irony'. ... Usually by people who don't actually know what Post-modernism actually is... ... Never mind. ... It wouldn't matter who sang the UK entry, ...
    (uk.music.folk)
  • Re: Death Penalty for Hackers???
    ... >the death penalty in my mind. ... What is breaking and entry and grand larceny? ... breaking and entry is a crime of physical presence. ...
    (alt.computer.security)
Loading