get notification if there is an error in scheduling time allocatio

[Guest]

1.I have a table which lists clients who need medical attention by
professional carers.
2. I have a second table which lists available carers.
3. I am designing a third table which is intended to allocate a carer to a
client. Call it the time allocation table. Each time allocated will have an
autonumber primary key, and will have a lookup to select the client, and
another lookup to allocate the carer.Then there will be a start time and a
finish time/date for the allocated period of care.
4.My question is this: Once a carer and a time period has been allocated in
a record, is there a way of preventing the allocation of the same carer to a
time period which overlaps or coincides with a previous allocation of the
same carer in another allocation record. it should generate an error message
or a "not available, this carer is already bookked during this period" type
message.
Any advice appreciated

Jim Normile

 

[Jamie Collins]

Once a carer and a time period has been allocated in
a record, is there a way of preventing the allocation of the same carer to a
time period which overlaps or coincides with a previous allocation of the
same carer in another allocation record.

Do a google groups search for the exact phrase, "You would be better
off looking for non-overlappping events". This post should also give
you some design hints e.g. using a separate Events table, that you
already have a natural key, etc.

Something to note is that MS Access/Jet's meaning of the term PRIMARY
KEY differs from that of other DBMS products and is linked to
performance rather than schema design. Google "Jet clustered index" for
more details.

Jamie.

--