Spread time across specific records

I don't think you have given us enough information. Questions:

Are trEmployeeNumber, trPhase, trCostCode, trHours your table fields?
How does it relate to the task? Is this the trPhase field? or the trCostCode field? How do you group an entry as there is no date field? Are there always 6 tasks?

In order to spread the hours you must know (for a given date) how many tasks were allocated some hours, how many were not allocated some hours.

In your example (you said half hours are OK) you would have to round down to 1.5 hours with the last task receiving the reminder, eg. 5x 1.5 + 2.5.

If you work in a loop, you could tally up the remainder from your initial divisor (1.67 in the example) and when greater that 0.5 hours add it to your allocation. This would then improve the allocation to:
1.5, 1.5, 2.0, 1.5, 1.5, 2.0

Hope that gives you some pointers. If you after code, you will need to give us more info...

Felix Burkhard

Thanks Borki.

Yes, those are my table fields. Yes, there is a date field, trDate; but I failed to list it. I am entering time into a subform that is queried based on the employee and date. The other fields I don't think really matter, but I provided them for additional information.

So after I have entered all records for a specific employee on a specific date I would like to run an update query, or code if better, that will count all of the records just entered (There won't always be 6, that was just an example) for the employee that have a null value for the time field and spread the time the most efficient way across those records.

You are correct that I do need to provide you additional information. I will need a text box, call it txtSpread for example, to enter the hours that need to be spread.

Your suggestion "If you work in a loop, you could tally up the remainder from your initial divisor (1.67 in the example) and when greater that 0.5 hours add it to your allocation. This would then improve the allocation to: 1.5, 1.5, 2.0, 1.5, 1.5, 2.0" is what I am looking for, but I don't know how to accomplish that.

I hope this provides enough additional information.

I wrote a bit of code for you. You need to add a text box and a button to your main form, probably below your subform. This is where you enter the hours to spread and the button to action it. You will need to create a event action for the button, so that you can paste the code below.

Warning, This is a bit of air code, meaning I have not actually run or tested it... I have provided ample comments, so you should be able to follow. Wtahc for line wrapping.

HTH

Felix

'Assume there is :
'- a button called btnSpread
'- a text box called txtHoursToSpread that contains hours to be spread
 
Private Sub btnSpread_Click()
   Dim rs As New DAO.Recordset
   Dim cSpread As Integer
   Dim dblSpreadHoursTotal As Double
   Dim dblSpreadHoursAlloc As Double
   Dim dblSpreadHoursDiv As Double
   Dim dblSpreadHoursRem As Double
   Dim dblSpreadHoursSum As Double
   Dim i As Integer
   
   'Error checking
   If Not (IsNumeric(Me.txtHoursToSpread)) Then
      Beep
      Exit Sub
   End If
 
   'Open a copy of the subform data
   Set rs = Me.subfrmTimeRecord.Form.RecordsetClone
   rs.MoveFirst
   cSpread = 0
 
   'Count the number of records where hours are null or zero
   Do While Not (rs.EOF)
      If (Nz(rs!trHours,0) = 0) Then cSpread = cSpread + 1
      rs.MoveNext
   Loop
   
   'Init variables
   dblSpreadHoursTotal = Me.txtHoursToSpread 'From the text box, user input!
   dblSpreadHoursDiv = dblSpreadHoursTotal / cSpread
   
   rs.MoveFirst
   i = 1
   dblSpreadHoursRem = 0#
   dblSpreadHoursSum = 0#
   Do While Not (rs.EOF)
      If (Nz(rs!trHours, 0) = 0) Then
         If (i = cSpread) Then
            'Last round, allocate remaining hours
            rs!trHours = dblSpreadHoursTotal - dblSpreadHoursSum
         Else
            'Rounding to nearest 0.5 value
            dblSpreadHoursAlloc = Int((dblSpreadHoursDiv + 0.25) * 2) / 2
            'Accumulate remainder
            dblSpreadHoursRem = dblSpreadHoursDiv - dblSpreadHoursAlloc
            If (dblSpreadHoursRem >= 0.5) Then
               'Adjust hours for remainder
               dblSpreadHoursAlloc = dblSpreadHoursAlloc + 0.5
               dblSpreadHoursRem = dblSpreadHoursRem - 0.5
            End If
            rs!trHours = dblSpreadHoursAlloc
            dblSpreadHoursSum = dblSpreadHoursSum + dblSpreadHoursAlloc
         End If
         rs.Update
      End If
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
   Me.subfrmTimeRecord.Form.Refresh
End Sub

Select allOpen in new window

Borki, we're very close.

Very good comments, easy to follow. I had to add rs.edit between lines 54 and 55 in your code above to remove an error message.

The time is being spread across, but it isn't rounding exactly right. In the original example, If I spread 10 hours across 6 records, 1.5 hours is entered for all 6 records, which of course doesn't add up to 10 hours.

If the spread would be an even spread with no remainder, such as 12 hours spread across 6 records, or 10 hours spread across 4 records it works perfectly. :-)

So, how do I fix?

Fascinating!! This works exactly as needed. Thanks for your help!

No worries, hope you learned a few things along the way!

Cheers

Felix