Appointment Database Avoid Duplicate Apointments

  • Thread starter Thread starter eddie.holder
  • Start date Start date
E

eddie.holder

Hi Guys

I am trying to create a database for a company, who organise
appointments to send engineers out to clients. However I need to
include data validation rule to stop the engineers from being sent to
the same or different clients at the same time and to avoid
duplicating appointments!

Currently, I have 3 tables a) Customers b) Engineers c) Appointments

The appointments table has AppID, Customer, Engineer (both relating to
the respective tables), Appointment Date and Appointment Time.

How can I stop the same engineer going to the same or other client on
the same date and time?

I am fairly new to Access and any help would be greatly appreciated?

Kind Regards,
Eddie
 
Simplest approach might be to make a multi-field unique indexes on the
relevant fields in the appointments table

Guessing at your rules, since you did not supply specifics
Rule 1) An engineer can have only one appointment with a customer per day.
--- Unique Index on Engineer, Customer, and Date fields

Rule 2) An Engineer can have only one appointment at a specific date and
time
--- Unique index on Engineer, Date, and Time fields
This one is more complex since you probably need to look for overlapping
dates and times and this cannot be done with any simple index or validation
rule in Access. You would need a query that checked for overlaps. Since
your appointments table does not have a duration for the visit or a start
AND end time for appointments, there is no solution that I can see to
prevent an engineer from having overlapping appointments

Rule 3) Only one engineer per customer per day
--- Unique index on customer and date fields.

All three of those will generate an error when you attempt to save the
record that would violate the rules of the index.

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Simplest approach might be to make a multi-field unique indexes on the
relevant fields in the appointments table

Guessing at your rules, since you did not supply specifics
Rule 1) An engineer can have only one appointment with a customer per day.
--- Unique Index on Engineer, Customer, and Date fields

Rule 2) An Engineer can have only one appointment at a specific date and
time
--- Unique index on Engineer, Date, and Time fields
This one is more complex since you probably need to look for overlapping
dates and times and this cannot be done with any simple index or validation
rule in Access. You would need a query that checked for overlaps. Since
your appointments table does not have a duration for the visit or a start
AND end time for appointments, there is no solution that I can see to
prevent an engineer from having overlapping appointments

Rule 3) Only one engineer per customer per day
--- Unique index on customer and date fields.

All three of those will generate an error when you attempt to save the
record that would violate the rules of the index.

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.












- Show quoted text -
Click to expand...

Hi John, thanks for the response. To answer some of your questions,
lets assume the following:

a) An engineers day is booked by 2 hour slots. 8-10, 10-12, 13-15,
15-17 '00 hours.
b) Any customer can book an engineer twice in a day
c) An engineer simply cannot be booked for the same time slot in the
same day.

My appointments table will allow the user to choose a time slot. If I
have another table called time slots with Id's, e.g. ID = 1 , Time =
8am, ID = 2, Time = 10am etc linked to the appointments table, so by
creating an appointment, you choose a time slot

Therefore, if I can query the time slots that are free for an engineer
on a day, I should be able to use that as the source for a combobox
for users to select from. Therefore, they will not be able to choose a
time slot that has already been used.

My query to thus far has Appointments table for engineer id, date and
from TimeSlots table for all timeslots in a day. The results will show
me all the bookings for an engineer, the date and what time slots. If
I can create another query to exclude the results of the previous
query, ie time slots that has been used, that should show me free time
slots

Any ideas?
 
Rule 2) An Engineer can have only one appointment at a specific date and
time
--- Unique index on Engineer, Date, and Time fields
This one is more complex since you probably need to look for overlapping
dates and times and this cannot be done with any simple index orvalidation
rule in Access. You would need a query that checked for overlaps.
Click to expand...

The boilerplate logic looks like this:

NOT EXISTS (
SELECT *
FROM MyTable AS T1, MyTable AS T2
WHERE T1.key_col = T2.key_col
AND IIF(T1.start_date > T2.start_date, T1.start_date, T2.start_date)
< IIF(T1.end_date > T2.end_date, T2.end_date, T1.end_date)
);

In other words, nothing that Access can't handle.

The problem is, the logic cannot be encapsulated in a Validation Rule
because they are either field-level or record-level (the latter is
confusingly referred to as a Table Validation Rule, presumably because
you can only have one per table and they are defined in the table's
properties page).

The Jet engine, however, has CHECK constraints that are truly table-
level i.e. they can comprise subqueries that reference values in other
rows in the same table (they can also reference other tables but,
unlike the MSDN articles on the subject, I would not recommend this).
CHECK constraints are not well used in the Access world which can
generally be attributed to a 'lack of visibility'.

Another approach is to integrate the logic into SQL procedures
(lowercase) that INSERT (append) and UPDATE rows in the table e.g.

CREATE PROCEDURE AddAppointent (
arg_employee_ID INTEGER,
arg_start_date DATETIME,
arg_end_date DATETIME
)
AS
INSERT INTO Appointments (employee_ID, start_date, end_date)
SELECT DISTINCT arg_employee_ID, arg_start_date, arg_end_date
FROM Appointments AS T1
WHERE NOT EXISTS (
SELECT *
FROM Appointments AS T2
WHERE T1.employee_ID = arg_employee_ID
AND IIF(arg_start_date > T2.start_date, arg_start_date, T2.start_date)
< IIF(arg_end_date > T2.end_date, T2.end_date, arg_end_date)
);

The 'records affected' should be examined to determine whether the
INSERT was successful.

The above approach does not have to be implemented literally as a
PROECDURE (uppercase = SQL keyword) in the engine ('parameter query'
in Access terms) -- the equivalent SQL can be 'squirted' in -- but
encapsulating the logic in a database engine object is perhaps best
because judicious use of permissions can ensure data integrity.

The above logic assumes the 'closed-open' representation of dates is
used e.g. an appointment that ends #2007-01-01 14:30:00# is not
considered to overlap with another that starts #2007-01-01 14:30:00#.
Note the use of 'Appointments AS T1' assumes the table is non-empty,
so it is perhaps wiser to use a small permanent auxiliary table e.g.
Constants is a common one.

Jamie.

--
 
b) Any customer can book an engineer twice in a day
Click to expand...

Does the restriction of two per day restriction apply to individual
customers or to the engineer? e.g. can the engineer be booked by four
different customers in the same day (one per slot)? or do you mean an
engineer can only have two appointments per day total? TIA.

Jamie.

--
 
Could you use the unique index situation as described and add the timeslot
field as appropriate.

Rule1 - Only one appointment for an engineer for any timeslot
Engineer, Date, TimeSlot

Rule 2 (?) Only one engineer per customer per time slot (?)
Customer, Date, TimeSlot

As for the free time slots query, that would probably look like the
following for any specific Engineer and date

SELECT TimeSlots.TimeSlot
FROM TimeSlots
WHERE TimeSlot NOT IN
(SELECT Appointments.TimeSlot
FROM Appointments
WHERE Engineer = "Thomas" AND Appointment.BookDate = #03/30/2007#)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Could you use the unique index situation as described and add the timeslot
field as appropriate.

Rule1 - Only one appointment for an engineer for any timeslot
Engineer, Date, TimeSlot

Rule 2 (?) Only one engineer per customer per time slot (?)
Customer, Date, TimeSlot

As for the free time slots query, that would probably look like the
following for any specific Engineer and date

SELECT TimeSlots.TimeSlot
FROM TimeSlots
WHERE TimeSlot NOT IN
(SELECT Appointments.TimeSlot
FROM Appointments
WHERE Engineer = "Thomas" AND Appointment.BookDate = #03/30/2007#)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.









- Show quoted text -
Click to expand...

Gentlemen, thank you for your help! You guys are heros!

Eddie
 
The boilerplate logic looks like this:

NOT EXISTS (
SELECT *
FROM MyTable AS T1, MyTable AS T2
WHERE T1.key_col = T2.key_col
AND IIF(T1.start_date > T2.start_date, T1.start_date, T2.start_date)
< IIF(T1.end_date > T2.end_date, T2.end_date, T1.end_date)
);

In other words, nothing that Access can't handle.

The problem is, the logic cannot be encapsulated in a Validation Rule
because they are either field-level or record-level (the latter is
confusingly referred to as a Table Validation Rule, presumably because
you can only have one per table and they are defined in the table's
properties page).

The Jet engine, however, has CHECK constraints that are truly table-
level i.e. they can comprise subqueries that reference values in other
rows in the same table (they can also reference other tables but,
unlike the MSDN articles on the subject, I would not recommend this).
CHECK constraints are not well used in the Access world which can
generally be attributed to a 'lack of visibility'.

Another approach is to integrate the logic into SQL procedures
(lowercase) that INSERT (append) and UPDATE rows in the table e.g.

CREATE PROCEDURE AddAppointent (
arg_employee_ID INTEGER,
arg_start_date DATETIME,
arg_end_date DATETIME
)
AS
INSERT INTO Appointments (employee_ID, start_date, end_date)
SELECT DISTINCT arg_employee_ID, arg_start_date, arg_end_date
FROM Appointments AS T1
WHERE NOT EXISTS (
SELECT *
FROM Appointments AS T2
WHERE T1.employee_ID = arg_employee_ID
AND IIF(arg_start_date > T2.start_date, arg_start_date, T2.start_date)
< IIF(arg_end_date > T2.end_date, T2.end_date, arg_end_date)
);

The 'records affected' should be examined to determine whether the
INSERT was successful.

The above approach does not have to be implemented literally as a
PROECDURE (uppercase = SQL keyword) in the engine ('parameter query'
in Access terms) -- the equivalent SQL can be 'squirted' in -- but
encapsulating the logic in a database engine object is perhaps best
because judicious use of permissions can ensure data integrity.

The above logic assumes the 'closed-open' representation of dates is
used e.g. an appointment that ends #2007-01-01 14:30:00# is not
considered to overlap with another that starts #2007-01-01 14:30:00#.
Note the use of 'Appointments AS T1' assumes the table is non-empty,
so it is perhaps wiser to use a small permanent auxiliary table e.g.
Constants is a common one.

Jamie.

--
Click to expand...

Jamie, sorry, bad english. I meant that a customer could book any
engineer more than once a day.
 
Back
Top