Multiple Indexing

I am trying to index 10 fields under one index, I have
read and followed the steps on the help files for
creating a multiple field index however it does not
appear to be working; the fields will allow duplicate
data without any error messages at any point.

I have tried deleting fields from the index to determine
the maximum number it is capable of working with
(although it specifies this is 10) however it does not
seem to work with any number greater than one.

I have also tried downloading your updates however I
cannot install service packs 2 and 3.

Can you please help me.

I've never had any problems with multi-field indexes. How are you creating
the index?

Oh, and it might help to know what version of Access you're using.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"CVL" wrote in message
...
I am trying to index 10 fields under one index, I have
read and followed the steps on the help files for
creating a multiple field index however it does not
appear to be working; the fields will allow duplicate
data without any error messages at any point.

I have tried deleting fields from the index to determine
the maximum number it is capable of working with
(although it specifies this is 10) however it does not
seem to work with any number greater than one.

I have also tried downloading your updates however I
cannot install service packs 2 and 3.

Can you please help me.

I am using Access 2000.

In the table I am selecting the index button then typing
in the index name in the first column then listing the
fields in the next column down 10 rows without writing in
the index name again.
-----Original Message-----
I've never had any problems with multi-field indexes.
How are you creating
the index?

Oh, and it might help to know what version of Access
you're using.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"CVL" wrote in message
...
I am trying to index 10 fields under one index, I have
read and followed the steps on the help files for
creating a multiple field index however it does not
appear to be working; the fields will allow duplicate
data without any error messages at any point.

I have tried deleting fields from the index to
determine
the maximum number it is capable of working with
(although it specifies this is 10) however it does not
seem to work with any number greater than one.

I have also tried downloading your updates however I
cannot install service packs 2 and 3.

Can you please help me.


.

Is this supposed to be the Primary Key, or simply a second unique index on
the table?

If it's a Primary Key, you can highlight the 10 fields in your table while
in Design mode and click on the Key icon on the button bar.

Otherwise, did you remember to set the index to Unique = Yes?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



wrote in message
...
I am using Access 2000.

In the table I am selecting the index button then typing
in the index name in the first column then listing the
fields in the next column down 10 rows without writing in
the index name again.
-----Original Message-----
I've never had any problems with multi-field indexes.
How are you creating
the index?

Oh, and it might help to know what version of Access
you're using.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"CVL" wrote in message
...
I am trying to index 10 fields under one index, I have
read and followed the steps on the help files for
creating a multiple field index however it does not
appear to be working; the fields will allow duplicate
data without any error messages at any point.

I have tried deleting fields from the index to
determine
the maximum number it is capable of working with
(although it specifies this is 10) however it does not
seem to work with any number greater than one.

I have also tried downloading your updates however I
cannot install service packs 2 and 3.

Can you please help me.


.

No it is not the primary key field. I have set the index to unique "yes" and
also Ignore Null values "yes". I have tried using the second option as No to
test if this has made any difference, unfortunately it has not.

You cannot evaluate a Null; as Null means not known or unknown.

If you select Yes Ignore Nulls then the index value of that record is
ignored completely.

If you select No then the index value of that record is included in the
index but you cannot evaluate an index containing

"Test", Null, Null, Null, Null, Null, Null, Null, Null, Null

as the same (or different) from

"Test", Null, Null, Null, Null, Null, Null, Null, Null, Null

As mentioned above Null means the value is not known.

If you wish to establish a unique index were every value in the table is
genuinely unique you will need to know the value of each field in the index.

Or at least know that the value of a field currently recorded as Null as
either a default value or say a zero length string.

You should note that a zero length string (or a default value) means that
you know the value should be nothing as opposed to unknown. If you set text
fields to "" you need to understand what this means in your design similarly
if numbers are set to 0 or -1 or whatever.

"Test", "", "", "", "", "", "", 0, 0, 0

and

"Test", "", "", "", "", "", "", 0, 0, 0

are identical records.

A bit confusing but just remember that Null does not mean nothing it means
unknown. Further you must remember the meaning of your default values and
without a DBMS like DB2 or SQL Server it is up to each application to manage
them for Jet. DB2 and SQL Server can use triggers to manage default values.

One should be very wary of a design that has a lot of nulls especially if
these are in an index.

--
Slainte

Craig Alexander Morrison

Quoting myself from 10 years ago:

"All in all Nulls are a pain and SQL and the products only make it worse. I
would always *try* to avoid them, Nulls that is (g), with careful database
design. The way they are implemented from product to product and from
version to version could easily vary. This, markers idea, was a mistake by
Codd, IMHO, and the vendors and ANSI jumped on it and we seem to be stuck
with it for now."





"CVL" wrote in message
...
No it is not the primary key field. I have set the index to unique "yes"
and
also Ignore Null values "yes". I have tried using the second option as No
to
test if this has made any difference, unfortunately it has not.

Right so if i now select ignore nulls to no it will record the values, and if
one of the fields is left empty on several occasions this will not read as a
duplicate it will simply ignore the entry?

No the opposite would be true. If ignore nulls is set to no then it would
not ignore nulls it would add them to the index but you could add as many as
you like if any of the fields are null. If ignore nulls is set to yes then
the records with nulls would not be added to the index.

No entry would be ignored. None could be designated as a duplicate because
it contained a Null.

Why have you got nulls? Is it possible to get rid of them with a different
design?

NOTHING is a duplicate if it contains a NULL. NULL does not equal NULL.

--
Slainte

Craig Alexander Morrison


"CVL" wrote in message
...
Right so if i now select ignore nulls to no it will record the values, and
if
one of the fields is left empty on several occasions this will not read as
a
duplicate it will simply ignore the entry?

I have tested the database using Ignore Null: No but it still ignores
duplicate data.

To help you understand why I have blank fields what I am trying to
accomplish involves vehicle registration numbers, these obviously cannot be
duplicates but i require 10 max to be processed at one time, however this
does not mean that it will always have 10 many entries will only contain one.

It won't make any difference as I explained.

It cannot evaluate a NULL as equal to another NULL

No matter what the setting it will not stop you recording what you may
perceive as a duplicate.

The only thing it does is include it in the index or not.

--
Slainte

Craig Alexander Morrison


"CVL" wrote in message
...
I have tested the database using Ignore Null: No but it still ignores
duplicate data.

To help you understand why I have blank fields what I am trying to
accomplish involves vehicle registration numbers, these obviously cannot
be
duplicates but i require 10 max to be processed at one time, however this
does not mean that it will always have 10 many entries will only contain
one.