Multiple Indexing

=?Utf-8?B?Q1ZM?= wrote in news:A55A52DF-
:

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

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.

If I understand your previous posts properly Craig what you are saying is
that a NULL is not a 0 or a blank entry but an unknown value. Therefore it
reads every object in the field as unknown and it cannot record the value of
something that is not known in the index.

I've now set the option to Ignore Nulls No. This means it should now record
the entries and because I have selected Unique to Yes it should recognise
duplications.

However it is not. It appears to be only working on a single index. I have
a 2 field multiple index working on a trial database which I wrote several
weeks in advance and it is working correctly, however if I now make a new
database of any sort it does not do the same, I have checked the options
against one another and they are identical.

Also as well as that I have tried to create the same thing on a different
computer under Access 2003 and have a simlar problem, however on that machine
it reads it as a single index rather than ignoring the index completely as it
is at the moment.

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.


"CVL" wrote in message
...
If I understand your previous posts properly Craig what you are saying is
that a NULL is not a 0 or a blank entry but an unknown value. Therefore
it
reads every object in the field as unknown and it cannot record the value
of
something that is not known in the index.

I've now set the option to Ignore Nulls No. This means it should now
record
the entries and because I have selected Unique to Yes it should recognise
duplications.

However it is not. It appears to be only working on a single index. I
have
a 2 field multiple index working on a trial database which I wrote several
weeks in advance and it is working correctly, however if I now make a new
database of any sort it does not do the same, I have checked the options
against one another and they are identical.

Also as well as that I have tried to create the same thing on a different
computer under Access 2003 and have a simlar problem, however on that
machine
it reads it as a single index rather than ignoring the index completely as
it
is at the moment.

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.

--
Slainte

Craig Alexander Morrison


"CVL" wrote in message
...
If I understand your previous posts properly Craig what you are saying is
that a NULL is not a 0 or a blank entry but an unknown value. Therefore
it
reads every object in the field as unknown and it cannot record the value
of
something that is not known in the index.

I've now set the option to Ignore Nulls No. This means it should now
record
the entries and because I have selected Unique to Yes it should recognise
duplications.

However it is not. It appears to be only working on a single index. I
have
a 2 field multiple index working on a trial database which I wrote several
weeks in advance and it is working correctly, however if I now make a new
database of any sort it does not do the same, I have checked the options
against one another and they are identical.

Also as well as that I have tried to create the same thing on a different
computer under Access 2003 and have a simlar problem, however on that
machine
it reads it as a single index rather than ignoring the index completely as
it
is at the moment.

=?Utf-8?B?Q1ZM?= wrote in news:7F6F98EC-
:


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

I can see where your coming from, however this would require the user to
enter in the data several times for one entry and also the information does
not need to be used anywhere else nor is it linked to anything else.

What I'm trying to achieve is saving the values of these 10 fields to one
place where duplications cannot occur.

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.

Unfortunately for processing reasons one number per entry is not a workable
solution. If there is another way to achieve the same thing by not using
indexes possibly by some coding placed upon a button which says put simply if
field 1 = field 2 display a messagebox to say Cannot process a duplication.
The problem with the coding is that I can see no way to search all the
previous records as well as the existing one.

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.

=?Utf-8?B?Q1ZM?= wrote in
:


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

On Wed, 1 Sep 2004 15:07:02 -0700, CVL
wrote:

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)