Multiple Indexing

[CVL]

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.

 

[Douglas J. Steele]

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.

 

[Guest]

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.

 

[Douglas J. Steele]

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?

 

[Craig Alexander Morrison]

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

 

[Craig Alexander Morrison]

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.

 

[CVL]

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.

 

[Craig Alexander Morrison]

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.

 

[Tim Ferguson]

but i require 10 max to be processed at one time,

This doesn't mean you have have ten fields side-by-side in the table does
it?

Tim F

 

[CVL]

Yes there are 10 fields for this data.

I understand that it will not pull up a blank record as a duplicate when
Ignore Nulls is set to No. However this is still not working with ANY data,
if i enter in the same data in 2 fields or over 2 records it does not
recognise it as a duplicate.

 

[Brendan Reynolds]

No, it is not that Jet can not record the value of something that is unknown
(i.e. Null) in an index. It can do that quite happily. The point is that
generally, there is little point in doing so.

The Jet database engine treats Null as an unknown value. That means that a
comparison of two Null values does not evaluate to True. Is one unknown
value the same as another unknown value? The answer is neither True nor
False, but unknown, i.e. Null. So I can have a unique index on the
combination of Field1 and Field2 and I can happily add multiple records all
with Null values in those fields. As far as Jet is concerned, these are not
duplicates, because Null = Null does not evaluate to True.

What this means in practise is that in a Jet (MDB) database it does not
usually make sense to allow Null values in any field that forms part of a
unique index. With some possible rare exceptions that I can't think of right
now, if you want a unique index, you will want to disallow Null values in
any of the fields included in that index.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Craig Alexander Morrison]

The following entries are not duplicates

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

and

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

If you set Unique to True and Ignore Nulls to True

Neither of the above records will be added to the index, they will be
recorded in the table.

If you set Unique to True and Ignore Nulls to False

Both records will be added to the index and they will be recorded in the
table.

It does not matter what you do with the Ignore Nulls setting as far as the
records in the table are concerned if any field contains a null. You do not
see the index you see the records in the table.

Consider using default values for each field and understand what these
default values represent.

Note: If you use SQL Server you should note that it "incorrectly" evaluates
a Null as equal to Null, this would allow you to achieve what you seem to
want however nulls are BAD and very very BAD in unique indexes.

I am not aware of any difference in the handling of this matter in Access
97, 2002 or 2003, Jet is more correct on this matter than SQL Server.

 

[Tim Ferguson]

Yes there are 10 fields for this data.

From the little I have understood about the main part of this thread, I
strongly suspect that you actually have a Major Design Problem. Although I
don't know much about what you are trying to model, I have a very strong
feeling that these ten fields should be ten records in another table.

these obviously cannot be
duplicates but i require 10 max to be processed at one time,

Yes, as I thought: almost certain these should be "normalised out"

Just a penny's-worth...


Tim F

 

[CVL]

Ok. So your saying the blank entries is the problem.

Why then when I create a new database and have 2 fields only plus a primary
key, multiple index the 2 fields and enter in details for both the fields on
every record does it still record say:

"Test, Test" and "Test, Test".

Is there prehaps an option in Access or an extra item I require to install
to run this properly?

Is there another way of ensuring the same value does not get entered into
the 10 fields throughout the database?

eg "Test" cannot be placed on another field on the record OR on another
field in ANY record.

 

[Tim Ferguson]

If I used a foreign key for this I would have to run two forms 10
times each, making it even more complicated than using a single index
where I would only need to run one 10 times.

No, of course it wouldn't. You can make the UI do whatever you want, and
the recommended practice is always to echo (or improve upon) the users'
favoured working practice. Your last comment in the original thread:

eg "Test" cannot be placed on another field on the record OR on another
field in ANY record.

really tells me that the whole design is badly wrong.

Best wishes


Tim F

 

[John Vinson]

Is there prehaps an option in Access or an extra item I require to install
to run this properly?

You can create a unique Index on any field *OR* on any combination of
up to ten fields, ensuring that no two records can have the same
combination of values. For instance if you had three fields A, B, and
C with a unique Index, you could add records:

A B C
1 2 3
1 2 4
1 3 4
1 3 3

because, though any field has duplicates, no two records have the same
set of values. Attempting to add another record

A B C
1 2 4

would fail with an index error.

To create such an index, open your table in design view and select the
Indexes icon (like a lightning bolt hitting a datasheet). Name the
index in the left column, and select one to ten fields in sequential
rows in the right. Specify that the index is unique.


John W. Vinson[MVP]
(no longer chatting for now)

 

[Craig Alexander Morrison]

You may be better off using a hammer rather than a spanner.

I would suggest that you read up on normalisation and then apply that to
your data. You may wish to check the Access 2002 Knowledge Base article
283878 dated 10 October 2003. (Referenced on MSDN Library DVD July 2004) or
online at http://support.microsoft.com/?id=283878

 

[CVL]

I will look further into possibly making another table and for the fields.

As for a design flaw with the data only being entered once it is because of
the data that is being delt with. The 10 fields are dealing with vehicle
registration numbers, because we can not work on a single vehicle more than
once these entries will not need to be used again.

 

[CVL]

It is particularly tempting to use a hammer against this problem I must admit.

I know normalisation is to remove many to many relationships, however these
fields dont include a relationship at the moment because it was not needed.

However if I try to go around the problem and create another table this will
become neccessary.

 

[John Vinson]

John you seem to understand exactly what I am trying to accomplish. However
the problem is that I am selecting the indexes icon, I am selecting one name
and then the fields and I have set the index to unique yet it ignores the
index completely as if it was not there.

On rereading the thread, it seems I did misunderstand. Do you mean
that if the value A123321 occurs in ANY of the ten fields, in ANY
record, then it should be prohibited in any field in any other record?

If so, a) an Index will not help and b) as suggested by the other
volunteers here, your table structure is INCORRECT and should be
revised.

John W. Vinson[MVP]
(no longer chatting for now)