Solved

Spread time across specific records

Posted on 2008-10-29
7
276 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:gedwardnelson
Comment Utility
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
Comment Utility
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
Comment Utility
Fascinating!! This works exactly as needed. Thanks for your help!
0
 
LVL 9

Expert Comment

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

Cheers

Felix
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access query grid query 10 19
Combobox issue 4 25
MS Access query date sorting question 7 11
Another multiple criteria puzzle 4 18
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now