cv****@gmail.com wrote:
Hi all
Im working on productivity calculations (Time calculations) and need
some help in coding.
Database Tool:MS-Access 2003.
The general operator punch-in time is 5:30 AM and the punch-out time is
2:00PM. The Break times are
1) 9:30 AM to 9:45 AM
2) 11:00AM to 11:30 AM
3) 12:30PM to 12:45 PM
Now, suppose an operator starts working on a fresh batch of parts @
7:00AM MONDAY morning and finishes the batch @ 7:00AM TUESDAY Morning,
I need to get answer something like this:
24 hrs(7:00AM Mon - 7:00AM Tue) MINUS 15 Minutes(9:30 AM - 9:45 AM)
MINUS 30 Minutes(11:00AM - 11:30 AM) MINUS 15 Minutes(12:30PM - 12:45
PM) MINUS 15hrs & 30Min (2:00PM Mon till 5:30AM Tue)
That is 7.5 hours.
Im creating a database of operators and 4 different operations and I
need this calculations to be made. The catch is that the operator could
start processing a fresh batch of parts any time of the day and could
finish any given time in a day.
I need to subtract the appropriate break times.
Any help in coding is greatly appreciated.
I would write a public function to count the number of 15 minute breaks
between two general punch-in and punch-outs that include the date and
time. The break from 11:00 AM to 11:30 AM would count as two 15 minute
breaks. Then the number of breaks divided by four should give the
number of hours to subtract for breaks. Try:
Public Function CountBreaks(dtIn As Date, dtOut As Date) As Integer
Dim BreakStart(5) As Date
Dim BreakEnd(5) As Date
Dim dtTest As Date
Dim intI As Integer
Dim NBreaksPerDay As Integer
NBreaksPerDay = 4
CountBreaks = 0
If dtIn >= dtOut Then Exit Function
BreakStart(1) = "9:30 AM"
BreakEnd(1) = "9:45 AM"
BreakStart(2) = "11:00 AM"
BreakEnd(2) = "11:15 AM"
BreakStart(3) = "11:15 AM"
BreakEnd(3) = "11:30 AM"
BreakStart(4) = "12:30 PM"
BreakEnd(4) = "12:45 PM"
'Back up starting time to beginning of 15 minute interval
dtTest = DateAdd("n", -DatePart("n", dtIn) Mod 15, dtIn)
Do While dtTest <= dtOut
For intI = 1 To NBreaksPerDay
If Format(dtTest, "hh:nn") = Format(BreakEnd(intI), "hh:nn") Then
CountBreaks = CountBreaks + 1
End If
Next intI
dtTest = DateAdd("n", 15, dtTest)
Loop
End Function
It would be used in a query something like:
SELECT CountBreaks(PunchIn, PunchOut) / 4 AS BreakTimeHrs,
DateDiff('n', PunchIn, PunchOut) / 60 - [BreakTimeHrs] AS ActualHours
FROM tblTimeTickets;
Perhaps an alternative SQL statement can be constructed that does the
counting instead. I didn't try to get clever by using the date
representation internals in order to provide a shortcut for some of the
calculations. The BreakStart and BreakEnd arrays can also be passed
into the function as arguments. Use the UBound function to get
NBreaksPerDay if you are passing in the arrays. Note: I didn't do much
testing of this function.
Now that we're warmed up, let's apply this to your problem.
For, say, Operator 1, Batch 1 and Operation 1:
PI = 7:00 AM Monday
PO = 2:00 PM Monday
PI = 5:30 AM Tuesday
PO = 7:00 AM Tuesday
So these need to be separate records in tblTimeTickets. Having Access
look for a separate Operation PI to get the PO is a little more
challenging but it's what I'm about to be asked to do.
The first calculation will take one hour away from the seven hours
worked. The second calculation will simply add the 1.5 hours worked
with no BreakTimeHrs subtracted. This suggests something like a query
that uses a SUM of ActualHours with GROUP BY OperatorID, BatchID,
OperationID. There are other potential twists as well that complicate
things when you try to implement this in the real world.
Hope this helps,
James A. Fortune
CD********@FortuneJames.com 
