Adjusting Goals for Time

[athyeh]

Hello,

Let me state the problem:

I have operators that are producing each hour.

I have created a form that operators (using a scanning wand) scan a barcode each time they complete a unit. The form is connected to a table that records various informations (selected on the form) regarding that unit and also the Time that the scan took place (the unit was completed).

As a motivation tool, the Hourly Goal (35) is posted in a text box next to the Counter (Counts production for the day [because I have not figured out a way to separate counting by hour]) that is also in a text box.

Operators switch between one of three processes a day [Drill Screws, Cut Wood, Bang Hammer (Respective Goals: 10, 15, 20)], start at different times (different shift types), take breaks at different times, and take lunch at different times.


I am looking for a way:
1. To create some type of logic or rule that tells the database that if:

• The Operator is working "Shift A":
• His breaks are at 9:30AM-9:15AM and 2:30PM-2:45PM
• His lunch is at 12:30PM-1:00PM
• He ends his day at 3:30PM
• THEREFORE his Adjusted Goals for the hours of 9:00-10:00 AM, 12:00 PM - 1:00 PM, 2:00 PM - 3:00 PM, 3:00 PM - 4:00 PM are (75%, 50%, 75%, 50%)
• GIVEN that he was working "Bang Hammer"
• And auto-display that in "Goal Textbox" on the form as he works through the day.

Example (Desired Functionality):

John Henry works "Shift A"


He starts the day at 8:00 AM
Process from 8:00 AM - 9:00 AM (Bang Hammer) [Goal Textbox]: 35 (100%)
Process from 9:00 AM - 10:00 AM (Drill Screws) [Goal Textbox]: 8 (75% of 10 rounded-up)

Now:

What if John Henry works switches shifts in between hours to line balance?

My first thought:

Create Table with "Hour of Production" as a drop-down.

User selects hour of production and based on the value in that box, Goal Textbox displays goal according to rules.

1) Is there a way to develop a rule that can either prevent user from selecting hours that he is not currently "in" for instance at 9:30 AM the only hour he would be able to select is 9:00 AM - 10:00 AM?

OR

What about looking at this from a time Elapsed (from start time) instead of actual time?

User presses a button at the start of the shift and ....................................?

Please assist.

[athyeh]

If anyone has any ideas please let me know.