Solved

Spread time across specific records

Posted on 2008-10-29
7
290 Views
Last Modified: 2012-06-21
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
Comment
Question by:gedwardnelson
  • 4
  • 3
7 Comments
 
LVL 9

Expert Comment

by:borki
ID: 22837436
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
 

Author Comment

by:gedwardnelson
ID: 22837526
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
 
LVL 9

Expert Comment

by:borki
ID: 22837906
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
Industry Leaders: 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!

 

Author Comment

by:gedwardnelson
ID: 22841565
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
 
LVL 9

Accepted Solution

by:
borki earned 500 total points
ID: 22845157
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
 

Author Comment

by:gedwardnelson
ID: 22846249
Fascinating!! This works exactly as needed. Thanks for your help!
0
 
LVL 9

Expert Comment

by:borki
ID: 22846324
No worries, hope you learned a few things along the way!

Cheers

Felix
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question