Multi-Field Index

[rn5a]

Assume that a MS-Access DB table has 5 columns - Col1, Col2, Col3,
Col4 & Col5.

I create a multi-field index using all the columns i.e. the index
looks like this


Col1 (UNIQUE = Yes)
Col2
Col3
Col4
Col5

Can someone please tell whether the order in which the columns are
placed in a multifield index) (for e.g. in the above e.g. Col1 comes
first followed by Col2 followed by Col3 followed by Col4 & finally
comes Col5) plays a role while records are being inserted in the DB
table? Or please point me to some articles to learn about the basics
of MS-Access multi-field index. In ther words, is the above multi-
field index the same as the following multi-field index (note the
change in column order)?

Col3 (UNIQUE = Yes)
Col5
Col2
Col1
Col4

I have googled on this topic but most of them tell about how to create
multi-field index (Click View-->Indexes menu, then enter a name for
the index in the 1st column in the Index Properties window, select a
field in the 2nd column, set the index UNIQUE property to Yes, add
more columns under the 1st selected field in the 2nd column but don't
give a name to the index again etc.etc..). I know how to create multi-
field indexes but I want to know how they work & implement uniqueness.

Thanks,

RON

 

[Tom Wickerath]

Hi Ron,

Running Microsoft Access 2000 by John Viescas includes some good information
on multi-field indexes. I imagine that he has included the same information
in later versions of this title.

I don't know that the order of the columns plays any role while inserting
records, but it certainly plays a role when querying the database.

"Access can use a multiple field index in a search even if you don’t provide
search values for all the fields, as long as you provide search criteria for
consecutive fields starting with the first field."

In the first example, you can include criteria for:

col 1 only
or
col 1 and col2
or
col1, col2 and col3
or
col1, col2, col3 and col4
or
col1, col2, col3, col4, and col5

and the index will be useful for speeding up your search and reducing
network traffic in a shared multiuser database. Only the last field can
include an inequality, such as >, >=, <, or <=.

However, the index would not be useful if you provided criteria for
discontinuous fields, for example, col1 and col3.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[rn5a]

Hi Ron,

Running Microsoft Access 2000 by John Viescas includes some good information
on multi-field indexes. I imagine that he has included the same information
in later versions of this title.

I don't know that the order of the columns plays any role while inserting
records, but it certainly plays a role when querying the database.

"Access can use a multiple field index in a search even if you don't provide
search values for all the fields, as long as you provide search criteria for
consecutive fields starting with the first field."

In the first example, you can include criteria for:

col 1 only
or
col 1 and col2
or
col1, col2 and col3
or
col1, col2, col3 and col4
or
col1, col2, col3, col4, and col5

and the index will be useful for speeding up your search and reducing
network traffic in a shared multiuser database. Only the last field can
include an inequality, such as >, >=, <, or <=.

However, the index would not be useful if you provided criteria for
discontinuous fields, for example, col1 and col3.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________










- Show quoted text -

Tom, searching for records isn't the main issue here. The main thing
when a data entry
operator enters records in the table the combination of which already
exists in the table,
he shouldn't be allowed to enter the same combination aggain. For e.g.
assume that the
1st record in the table is Col1=1, Col2=2, Col3=3, Col4=4 & Col5=5.
Next if I try to enter the
same combination again i.e. Col1=1, Col2=2, Col3=3, Col4=4 & Col5=5,
then I shouldn't be
allowed to enter the same combination.

 

[Larry Linson]

Can someone please tell whether the
order in which the columns are placed
in a multifield index) (for e.g. in the
above e.g. Col1 comes first followed by
Col2 followed by Col3 followed by Col4
& finally comes Col5) plays a role while
records are being inserted in the DB
table?

I don't understand what you mean by "order . . . plays a role". What
concerns you, or what has led to a concern, and what is your concern? What
bad thing are you anticipating might happen, and what do you think your
options are to prevent it happening?

For the index to be "duplicate", each of the fields must have a value equal
to the corresponding field in an existing record.


Larry Linson
Microsoft Access MVP

 

[Tom Wickerath]

Hi Ron,

If that is your only concern, then the two versions that you indicated in
your initial post will work equally well.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[rn5a]

Hi Ron,

If that is your only concern, then the two versions that you indicated in
your initial post will work equally well.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________





- Show quoted text -

Tom, you have shown a lot of concerns about my concerns & I genuinely
appreciate y
our concerns. Can you please go to the following URL

http://www.utteraccess.com/forums/s...1413634&page=0&view=collapsed&sb=5&o=&fpart=1

to have a detailed explanation of what I need. The main reason I am
requesting you to visit the URL because I have even attached the (MS-
Access) database at that URL. Also Ihe attached Access table has the 3
indexes already created that prevent a combination of duplicate
ClassID, StartTime & EndTime records (I know there's a lot of
difference between MS-Access & SQL Server but atleast I might get some
ideas on how to go about it in Access).

Though you will be allowed to view the post at the above URL without
logging in, one has to login to download the attachment. If you aren't
a registered user at that site, then please use my login credentials
given below to download the attachment:

login name: 'rn5a' (without the quotes)
Password: 'zEHyFn' (again without the quotes)

Sorry for the inconvenience but I am really at my wits end to resolve
this problem. After having given my login name & password of that
site, I hope you can understand how desperately I am want a concrete
solution. So whoever sees this post, I would earnestly request them to
PLEASE visit the above URL (which an MS-Access DB forum).

 

[Tom Wickerath]

Hi Ron,

You went to great lengths in your reply to HiTechCoach, however, you did not
indicate whether you attempted to implement the advice given, ie. Allen
Browne's "Clashing Events/Appointments" article. I downloaded your sample (I
have my own UA Membership), however, the closest table that I can find that
matches what you indicated in your initial post here, ie:

I create a multi-field index using all the columns i.e. the index
looks like this

Col1 (UNIQUE = Yes)
Col2
Col3
Col4
Col5

is your BookingDetails table. However, this table has (5) single field
indexes, only one of which is unique, not one unique multi-field index made
up of 5 fields as indicated above. In any case, I think you would have a hard
time getting the unique index idea to work, because it would be possible for
a user to skirt the intent by simply entering a start time in a second,
essentially duplicate, record by just changing the start or stop time by one
minute.

I have a sample that does prevent overlapping entries for work time by
students. It uses a fair amount of VBA code to achieve this. If you'd like, I
can post a sanitized version tomorrow evening (actually, that would be later
tonight, since it's already tomorrow for me). I cannot take the time to do it
right now, because I'd need to clear customer data out of the database, and
change other identifying information, such as logos, etc. I'm at the end of
my evening now (it's nearly 1:15 AM for me), so I just cannot spend any more
time on this now. Sorry.


By the way, "Description" in your Classes table is a reserved word. You will
be doing yourself a huge favor if you avoid the use of any reserved words
when you assign names to things in Access. Here is a link that I recommend
that you bookmark. Download Allen's free utility as well, to test your
database:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Oh, and one more thing. The tone of your opening post in the UA group was
enough to make most folks just skip the post and not even consider helping
you. I'm referring to this sentence, in particular:

"I never expected this to be such a tough question to answer but I guess I
was wrong since none of the so-called Access MVPs & Access experts & gurus
could answer it in the 7 MS-Access forums & newsgroups where I have posted
this problem!"

As desperate as you might be, that kind of talk is just going to put most
folks off.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[rn5a]

Hi Ron,

You went to great lengths in your reply to HiTechCoach, however, you did not
indicate whether you attempted to implement the advice given, ie. Allen
Browne's "Clashing Events/Appointments" article. I downloaded your sample (I
have my own UA Membership), however, the closest table that I can find that
matches what you indicated in your initial post here, ie:


is your BookingDetails table. However, this table has (5) single field
indexes, only one of which is unique, not one unique multi-field index made
up of 5 fields as indicated above. In any case, I think you would have a hard
time getting the unique index idea to work, because it would be possible for
a user to skirt the intent by simply entering a start time in a second,
essentially duplicate, record by just changing the start or stop time by one
minute.

I have a sample that does prevent overlapping entries for work time by
students. It uses a fair amount of VBA code to achieve this. If you'd like, I
can post a sanitized version tomorrow evening (actually, that would be later
tonight, since it's already tomorrow for me). I cannot take the time to do it
right now, because I'd need to clear customer data out of the database, and
change other identifying information, such as logos, etc. I'm at the end of
my evening now (it's nearly 1:15 AM for me), so I just cannot spend any more
time on this now. Sorry.

By the way, "Description" in your Classes table is a reserved word. You will
be doing yourself a huge favor if you avoid the use of any reserved words
when you assign names to things in Access. Here is a link that I recommend
that you bookmark. Download Allen's free utility as well, to test your
database:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Oh, and one more thing. The tone of your opening post in the UA group was
enough to make most folks just skip the post and not even consider helping
you. I'm referring to this sentence, in particular:

"I never expected this to be such a tough question to answer but I guess I
was wrong since none of the so-called Access MVPs & Access experts & gurus
could answer it in the 7 MS-Access forums & newsgroups where I have posted
this problem!"

As desperate as you might be, that kind of talk is just going to put most
folks off.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________








- Show quoted text -

Yes, Tom, I know I was a bit harsh but believe me, I have been working
just on this since last 2 days without getting a concrete solution. I
must have gone through thousands of articles & posts but none of them
helped me. I got very very frustrated & fed up & I believed spat it
out at that forum. Sorry for the same.

I finally could manage what I wanted by modifying the 3 indexes,
thanks to a gentleman named Jon. Ihave attached the Access DB table at
that forum but a conflict still exists. Consider the following 2
records:

------------------------------------------------------------------
TeacherID ClassID VenueID DateAvail StartTime EndTime
------------------------------------------------------------------
1 1 1 5/15/2007 6:00 AM 8:00 AM
2 1 1 5/15/2007 7:00 AM 9:00 AM
------------------------------------------------------------------

The 1st row says that TeacherID=1 will teach ClassID=1 at VenueID=1 on
15th May, 2007 from 6AM to 8AM. The 2nd row says that TeacherID=2 will
teach ClassID=1 at VenueID=1 on 15th May 2007 from 7AM to 9AM. Now
ClassID=1 is already booked on 15th May 2007 from 6AM to 8AM which
means at 7AM, ClassID=1 will be taught by TeacherID=1. So how can
TeacherID=2 start a class from 7AM?

Any idea how do I overcome this conflict?

Thanks,

Regards,

RON