• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Spread time across specific records

I have a time entry database and I need the ability to be able to spread time across selected records.  
Let's say I have entered the time for an employee and they spent 10 hours working on 6 tasks, I'd like to spread that time evenly across all records that have a null value for the hour field. Additionally, I need the hours rounded up or down for each record to the closest .5 hours, with the difference being made up on the last records. I would activate this using a cmd. button.

For example, the above scenario would average out to 1.667 hours each. I'd like to break it down to 2, 2, 2, 2, 1, 1, or something similar.

The form name is subfrmTimeRecord
The relevant fields are trEmployeeNumber, trPhase, trCostCode, trHours.

Basically, The subform is queried based on the employee number, and I need to spread the hours only across each null value in the hours field. I may have other time entered for this employee already.

Thanks.

0
gedwardnelson
Asked:
gedwardnelson
  • 4
  • 3
1 Solution
 
borkiCommented:
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
0
 
gedwardnelsonAuthor Commented:
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.

0
 
borkiCommented:
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

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gedwardnelsonAuthor Commented:
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?

0
 
borkiCommented:
Sorry for the aircode, I did not have Access handy when I was typing it to actually test. I have done a sample DB & Form and it works now. I include the revised code.

The error was with the book keeping of the remainder, I forgot to accumulate it and action if there was a rounding the other way. I did a number of tests and it should work now as expected

Good luck!

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 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)
      rs.Edit
      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 = dblSpreadHoursRem + dblSpreadHoursDiv - dblSpreadHoursAlloc
            If (dblSpreadHoursRem >= 0.5) Then
               'Adjust hours for remainder
               dblSpreadHoursAlloc = dblSpreadHoursAlloc + 0.5
               dblSpreadHoursRem = dblSpreadHoursRem - 0.5
            ElseIf (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
         i = i + 1
      End If
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
   Me.subfrmTimeRecord.Form.Refresh
End Sub

Open in new window

0
 
gedwardnelsonAuthor Commented:
Fascinating!! This works exactly as needed. Thanks for your help!
0
 
borkiCommented:
No worries, hope you learned a few things along the way!

Cheers

Felix
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now