forecast job completion times

[diverse1]

Problem:
Create an Access form to show anticipated completion times of workload during the course of a day which takes into account coffee breaks, lunch breaks and shift change. This will allow for easier manpower allocation. Currently performing this in Excel but would like to migrate this to Access where it is possible for a number of people at different work stations to view the data simultaneously in real time.


Summary:
Our facility has a daily workload which is divided into “batches”. Each batch can last for anywhere from 15 minutes to 3 hrs depending on the customer requirements. Each batch is scheduled consecutively and the “batch time” for a given batch may be longer or shorter than a previous or subsequent batch.
I would like to create a “forecast” in the form of an access form or report which shows the start time and forecasted finish time of each batch for the course on an entire day.
Sample Data:
Batch 1 - 57 minutes
Batch 2 - 102 minutes
Batch 3 - 91 minutes
Batch 4 - 128 minutes
Batch 5 - 77 minutes
Batch 6 - 145 minutes
Batch 7 - 125 minutes
Batch 8 - 31 minutes

Operating milestones:

Start of Day 08:00
Day Shift Coffee break: 10:00 – 10:15 (15 minutes)
Day Shift Lunch Break: 12:30 – 13:00 (30 minutes)
Day Shift END: 15:45
Afternoon Shift START: 16:00 (15 minutes down between shifts)
Afternoon Shift Coffee Break: 18:00 – 18:15
Afternoon Shift Lunch Break: 20:30 – 21:00
Afternoon Shift END: 23:45
Midnight Shift Start: 00:00


I have been trying to accomplish this in a query by firstly decimalizing the times and then establishing when Day shift Coffee occurs and using an IIF statement: Dcoffee: IIf([expr2] Between 0.42708 And 0.52,CDate([expr2]+0.010417),CDate([expr2])) and doing the same thing for the rest of the breaks, lunches and shift changes but I’m thinking there must be a better way.

Eventually, I would like to be able to enter actual batch completion times onto this form in order to update the query and recalibrate the forecast in the event we have downtime or complete a batch earlier than anticipated.

Any thoughts anyone?

[goodguy]

From the way I see it, this will need to be done in VBA code.
However, in order for us to help you, we need a sample of your source data and your desired end-result, else we will just be shooting blind.