Identify Missing Time Ranges and create a record to account for it

Hello Everyone,

I have a scheduling system currently in use that is working fine, but I have
been asked to improve upon it to reduce the manual additions made by the
scheduler.

It is set up with a predetermined number of shifts that vary in start times
and end times that are stored in a template table. When the user creates a
new schedule for a day, it grabs the active schedules held in the template
and appends it to the schedule for the day. The person administering the
schedule then assigns the staff to the position and post the schedule. Works
great, simple, and everyone was very happy.

However, what seems to be occuring more frequently, is that employees are
having their shifts broken up due to training, or other assignments which
leaves gaps within the standard template. The scheduler always has had the
ability to edit the start and end times and manually add shifts as necessary
to compensate for this. I am looking to see if I can make Access identify
these gaps programatically and automatically create a new shift for the gap
that was generated on the change.

For example, say a shift is scheduled between 6am-6pm. Employee "A" is
typically scheduled for the shift, but on a specific day, he will need to
attend a training from 12pm-2pm and will be unavailable to cover the shift
for that specific time frame, but will return following the training. At
this time, the scheduler would edit the end time to 12pm and manually add the
two remaining shift times (12pm-2pm and 2pm-6pm respectively) and assign
Employee B to cover the "training time" and assign Employee A back to the
shift from 2pm-6pm. It is important and required that it be set up this way
because of internal reasons...Belive me I have suggested better ways.

Is there anyway that I can have the access identify the gaps in coverage when
the dates are changed and then create a new record automatically to cover the
gap. This is their "dream" as it would reduce a lot of additional entries by
the scheduler.

The layout of the database is summarized below:

Tbl_Schedule_Template
TemplateID_pk
TemplateStatus_fk
ShiftStartTime
ShiftEndTime

When the shift is scheuled, it appends these records into the following two
tables:

Tbl_Schedule
ScheduleID_pk
ScheduleDate

Tbl_Schedule_Details
ScheduleDetailID_pk
ScheduleID_fk
TemplateID_fk (populated if generated from the template, blank if manually
generated)
ScheduledStartTime
ScheduledEndTime
EmployeeID_fk

I appreciate any guideance you guys can give me. I have looked all over to
try and come up with a solution, but have not been successful.

--
Message posted via http://www.accessmonster.com