I am creating a database to track payroll information. Employees are assigned to a Home Dept but can work in other departments. I'm at the point where I'm trying to calculate overtime. I think I've got it figured out if they only work in 1 other department during a day. My problem is if they work in 2 or more other departments in 1 day.
Here's what it looks like on the form:
Here's what I've come up with that I think will work if there is only 1 other department:
Here's what it looks like on the form:
Code:
(on a subform)
Home Dept: 555
[b]
Date Start1 Stop1 Start2 Stop2 Hours
[/b]
01/17/2005 08:00 AM 12:00 PM 4
01/18/2005 01:00 PM 05:00 PM 05:30 PM 07:30 PM 6
01/19/2005 08:00 AM 12:00 PM 01:00 PM 05:00 PM 8
01/20/2005 08:00 AM 12:00 PM 4
[COLOR=red]01/21/2005 01:00 PM 05:00 PM 4[/color]
(on a subform)
Temp Departments
[b]
Dpt Date Start1 Stop1 Start2 Stop2 Hours
[/b]
512 01/17/2005 01:00 PM 05:00 PM 05:30 PM 07:30 PM 6
512 01/18/2005 08:00 AM 12:00 PM 4
512 01/20/2005 01:00 PM 05:00 PM 05:30 PM 07:30 PM 6
[COLOR=red]550 01/21/2005 08:00 AM 12:00 PM 4
512 01/21/2005 05:30 PM 07:30 PM 2[/color]
Code:
If HomeWorkDate = TempWorkDate then
if TotalHoursWorked > 40 then
if isnull (HomeDeptStop2) and isnull (TempDeptStop2) then
if HomeDeptStop1 > TempDeptStop1 then
if PreviousRunningTotal + HoursWorked > 40 then
if 40 – PreviousRunningTotal < 0 then
OT = HoursWorked
Else
OT = 40 – PreviousRunningTotal
End If
Else
OT = 0
End If
Else
OT = 0
End If
End If
if NOT isnull (HomeDeptStop2) and NOT isnull (TempDeptStop2) then
if HomeDeptStop2 > TempDeptStop2 then
if PreviousRunningTotal + HoursWorked > 40 then
if 40 – PreviousRunningTotal < 0 then
OT = HoursWorked
Else
OT = 40 – PreviousRunningTotal
End If
Else
OT = 0
End If
Else
OT = 0
End If
End If
if NOT isnull (HomeDeptStop2) and isnull (TempDeptStop2) then
if HomeDeptStop2 > TempDeptStop1 then
if PreviousRunningTotal + HoursWorked > 40 then
if 40 – PreviousRunningTotal < 0 then
OT = HoursWorked
Else
OT = 40 – PreviousRunningTotal
End If
Else
OT = 0
End If
Else
OT = 0
End If
End If
if isnull (HomeDeptStop2) and NOT isnull (TempDeptStop2) then
if HomeDeptStop1 > TempDeptStop2 then
if PreviousRunningTotal + HoursWorked > 40 then
if 40 – PreviousRunningTotal < 0 then
OT = HoursWorked
Else
OT = 40 – PreviousRunningTotal
End If
Else
OT = 0
End If
Else
OT = 0
End If
End If
Else
OT = 0
End if
End if
In order to check multiple Temp Depts, I'm thinking I need to somehow loop through a recordset of the TempDept table looking at dates and stop times but I'm not really good with recordsets. After coming up with the above my brain is fried, I'm getting a cold, and I can't figure it out.
Can some kind soul help me?
Debbie