Adjusting Dates with cap

[tuxalot]

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 txtRestrictedWo rk bound to
TblEmployeeInju ry.AwayFromWork and TblEmployeeInju ry.JobTransferO rRestiction
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.Val ue = 130
txtRestrictedWo rk.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.Val ue = 132
(130 original A total) plus 2 (bring remainder back into sum)

and

txtRestrictedWo rk.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.

[NeoPa]

You may find someone who is willing to take you further along this path.

I will limit myself at this time to linking you to an article (Normalisation and Table structures) which explains why this approach (updating and storing calculated values) is such a bad idea and is likely to leave you with complicated problems such as this one.

[ADezii]

I agree with NeoPa in that this problem is a lot more complicated than initially envisioned, especially since your data is not in a 'Normalized' State. If, for whatever reason, you wish to keep your data and structure in its current State, I would request that you display some sample Test Data, say 12 Records for a specific individual. You have previously defined your Table Name, along with the Fields that comprise it. Just some realistic data, and what the outcome from this data should be, will do the trick.

[tuxalot]

Hi All, thanks for the response. For my project, the database consists of 17 tables and the one mentioned in this post is the only one arranged like this. I read the post about Normalizing and it was very informative so thanks for that. I would be interested in tips on how I can store this data using a different structure. Based on your comments, it seems that this change would make a calculation such as is mentioned here a bit easier.

I only have sample data in this database, so I will create some more entries and attach the db to a post in this thread this weekend for your review.

Thanks again for your help.

[ADezii]

@tuxalot
You are quite welcome, I'l be checking in again during the weekend. Itm is probably a good idea to provide the specifics of your Employee Table, such as: Table Name, Field Names, Field Data Types, Validation Rules, etc.

[tuxalot]

Thanks. Here's something to chew on while I prepare a response to your previous post. This is what I need to do in a nutshell.

if sum(A+B)<180, determine if there are any remainder days to be had. If not, do nothing. If so, look at remainder column for the active EmployeeID and beginning with the oldest date, add remainder days (A or B, doesn't matter) back into the Sum(A+B) up to a total of 180. If we reach 180 then revert back to placing remainder data back into A & B remainder columns for all loss days going forward.

Thanks ADezii for offering HOPE.

Tux

[ADezii]

@tuxalot
tuxalot, this will probably be a long, drawn-out process, involving restructuring and figuring out an Algorithm that will efficiently accomplish what you are requesting (no clue what that is at this point). I'll do the best I can to help you with this Project, but I cannot guarantee anything beyond that. If all this is acceptable, and if there are no time constraints, then we can move on.

[tuxalot]

Sounds like a plan. Where shall we begin?

[ADezii]

@tuxalot

1. Sample data (Test Records) from tblDateHistory.
2. Name of your Employees Table along with all the Field Names and their Data Types which comprise the Employees Table.
3. Sample data from the Employees Table representing 3 Employees (don't have to be real, can be fictitious). This data should match corresponding Records in tblDateHistory.

[tuxalot]

Hi ADezii,

I've re-thought how to go about this, and I think I am going to do this at report time as this is really when the cap days at 180 is required for OSHA recordkeeping purposes. Let's close the thread on this for the time being. I may be able to bang together a query to get this done at report time.

Thanks. Sorry if I burned up a few brain cells :)