I have a table (TblDateHistory) to record lost days due to injuries:
- EmployeeID StartDate ReturnDate RestrictedOrLostDays TotalDays
-
-
1 ... ... Lost Time Days 36
-
1 ... ... Restricted / Transfer Days 50
-
2 ... ... Lost Time Days 10
-
3 ... ... Restricted / Transfer Days 8
-
1 ... ... Lost Time Days 36
-
3 ... ... Lost Time Days 3
-
3 ... ... Restricted / Transfer Days 14
-
2 ... ... Lost Time Days 6
-
1 ... ... Lost Time Days 60
Assume the dates above are sorted ascending.
I have two textboxes txtLostTime and txtRestrictedWork bound to
TblEmployeeInjury.AwayFromWork and TblEmployeeInjury.JobTransferOrRestiction
which keep running totals of each lost time day, so this bit works.
The function that updates the textboxes is as follows:
-
Public Function fSumDaysOff(strLostdays As String) As Long
-
'Feed in to variable strLostdays either "Lost Time Days" or
-
'"Restricted / Transfer Days" where apropriate
-
'http://www.oreilly.com/pub/h/3323
-
'Chr(34) = "
-
-
Dim strSQL As String
-
Dim strSQL1 As String
-
Dim strSQL2 As String
-
Dim strSQL3 As String
-
Dim strSQL4 As String
-
Dim strSQL5 As String
-
Dim strSQL6 As String
-
-
strSQL1 = "SELECT Sum([ReturnDate]-[StartDate]) AS SumDaysOff "
-
strSQL2 = "FROM [TblDateHistory] "
-
strSQL3 = "GROUP BY [RestrictedOrLostDays], [EmployeeID] "
-
strSQL4 = "HAVING ((([RestrictedOrLostDays])="
-
'strLostdays
-
strSQL5 = ") AND (([EmployeeID])="
-
'EmployeeID
-
strSQL6 = "))"
-
-
strSQL = strSQL1 & strSQL2 & strSQL3 & strSQL4 & Chr(34) & strLostdays _
-
& Chr(34) & strSQL5 & Forms!FrmMain!.EmployeeID & strSQL6
-
-
Dim conn As ADODB.Connection
-
Set conn = CurrentProject.Connection
-
Dim rs As New ADODB.Recordset
-
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
-
'MsgBox rs.Fields("SumDaysOff")
-
fSumDaysOff = rs.Fields("SumDaysOff")
-
rs.Close
-
Set rs = Nothing
-
Set conn = Nothing
-
-
If IsNull(EmployeeID) Then Exit Function
-
-
End Function 'fSumDaysOff
-
What needs to happen:
Assume for the sake of brevity:
A = Lost Time Days
B = Restricted / Transfer Days
If and when the sum of A+B reaches 180, stop counting.
Here's the tricky part. Example: If the current Sum A+B=179 and an entry of B is recorded with a value of 6, I need to add 1 to the Sum A+B to reach 180, and record the remaining 5 in a "Remainder" field in the table. Why you ask? Because the user can go back and make corrections to A & B row entries, and if a table row is deleted or changed I need to add the remainder back to the sum(A+B).
Example using the data above:
EmployeeID 1:
A=132
B=50
Sum=182. We have reached our cap of 180, so the two text boxes on my form would read:
txtLostTime.Value = 130
txtRestrictedWork.Value = 50
2 Total Days from the last table row above would be placed in a "remainder" column (i.e. from the total of 60, 58 were added to reach 180 leaving an 'A' remainder of 2).
Now a user goes back and makes some changes. Say they change row 2 in the table at the top to Restricted / Transfer Days=45 from the original total of 50.
Now the two text boxes would read:
txtLostTime.Value = 132
(130 original A total) plus 2 (bring remainder back into sum)
and
txtRestrictedWork.Value = 45 (50 original B total) minus 5 (reduction in row 2).
Earlier dates take precedence, so if remainder days are added back into the sum earlier remainder dates should be taken first.
I tried to explain this as clear as I can and I hope it makes sense. It is real complicated to me, being new to Access but hopefully someone has a solution.
Thanks very much for taking a look.
