"Index: No Duplicates"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can the "No duplicates" property in the Table Design be set to reference the
primary key?

I have a field (Staff) that contains a lookup column with 100 staff names in
it.
I would like each staff members name to be infinitly useable, PROVIDING the
date and time fields associated with it are not duplicated.

ie.
once "Apr. 19" , "12:00 PM", and "John Smith" have been associated, I would
like that to be the ONLY time those 3 exact entries can be made.

"John Smith" would be an acceptable entry at "12:01", on the 19th, or 12:00
on the 20th... but never again at 12:00 on the 19th.

Simply selecting "No Duplicates" bars me from ever using that specific entry
again, regardless of other data contained within that row of the table.

This has had me stumped for a while now... I have tried many approaches to
this.. and am having little luck.

If anyone has any solutions... please feel free to let me know.
This table is linked to forms as well as queries... so a solution using a
form or query capability would also be an option... if one existed.

Cheers!
and Thanks in Advance!!!
 
Trial & Error said:
Can the "No duplicates" property in the Table Design be set to
reference the primary key?

I have a field (Staff) that contains a lookup column with 100 staff
names in it.
I would like each staff members name to be infinitly useable,
PROVIDING the date and time fields associated with it are not
duplicated.

ie.
once "Apr. 19" , "12:00 PM", and "John Smith" have been associated, I
would like that to be the ONLY time those 3 exact entries can be made.

"John Smith" would be an acceptable entry at "12:01", on the 19th, or
12:00 on the 20th... but never again at 12:00 on the 19th.

Simply selecting "No Duplicates" bars me from ever using that
specific entry again, regardless of other data contained within that
row of the table.

This has had me stumped for a while now... I have tried many
approaches to this.. and am having little luck.

If anyone has any solutions... please feel free to let me know.
This table is linked to forms as well as queries... so a solution
using a form or query capability would also be an option... if one
existed.

Cheers!
and Thanks in Advance!!!
Click to expand...

You can create an index on multiple fields, and make that index a unique
index, allowing no duplicates. Although the only *obvious* way to do
this is to make that index be the primary key (that is, a
multiple-field -- or "compound" primary key) -- and maybe that's what
you want, I can't tell -- you can also make a non-primary-key, compound
index. With the table open in Design View, click the indexes button on
the toolbar, enter a new index name in the dialog, and enter (on that
line and succesive lines) the fields that are part of the index. Also
set the index properties in the bottom part of the dialog.

Note: it looks like you're using separate fields for date and time. It
would probably be more efficient to have one date/time field, holding
both the date *and* the time of day that you are interested in.
However, that would not be so efficient if you frequently search by
time, since then the time has to be extracted from the date/time field.
 
Thanks Dirk...

Just tested that out... worked like a charm.
You just saved me from pulling out most of my hair.

Just by chance,
is there any way to alter the auto message that Access displays when you try
to generated a conflict with this index I have now set up?

The message that pops up will likley scare most of the users who will be
using this DB... I would love to change it to somthing like " Please select a
different "Request" This one is already used at this time and date"

And as for the combined time and date...
I agree... I actually recieved a lot of advice on combining the two...
However I realized that the majority of use on this DB will require users to
pull up entries made for each hour of each day, or specific days, or blocks
of specific hours over the course of a month etc.
As such, splitting the two saved some headaches.
 
Trial & Error said:
Thanks Dirk...

Just tested that out... worked like a charm.
You just saved me from pulling out most of my hair.
Click to expand...

Hah! Too late for me!
Just by chance,
is there any way to alter the auto message that Access displays when
you try to generated a conflict with this index I have now set up?

The message that pops up will likley scare most of the users who will
be using this DB... I would love to change it to somthing like "
Please select a different "Request" This one is already used at this
time and date"
Click to expand...

I believe you can trap this in the form's Error event. I don't know
offhand what the error number is, but you can find out by ... trial and
error. <g>
 
Back
Top