Rounding question

[bailey11]

I have a formula:

TPUnit=(Units/SumofUnits)*10

The point of the formula is that truck pay can not exceed 10 hours per day even if the labor does. The problem is rounding. It need to round the TPUnit total to a factor of 0 decimals, but in the case where the employee worked 11 hours, it is giving me (2.73) 3, (2.73) 3, & (4.55)5 TP units for the jobs. How do I round to get the 10 hours only.

Thanks, Bailey 11

 

[dhookom]

Could you share some records where you are identifying the field names? You mention hours but your expression has units and sumofunits. You aren't making much sense to my old brain.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Sorry about that.

THe units are the number of hours the employee has worked on a particular job on a particular day.

The SumofUnits is the sum of all the hours he has worked for a day.

In the case above,

Joe blow worked 3.00 hours on job 1, 3.00 hours on job 2.00 & 5 hours on job 3

If an employee works over 5 hours for the whole day, they get 10 hours of truck pay spread across the jobs they worked as a ratio bases on the (units/sumofunits).

Help any?

 

[dhookom]

I fail to see some records with actual expected results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

THe result would be a number with no decimals, the total for the day not to exceed 10.

3,3,4 or 2,2,6 or so

 

[traingamer]

You'll could write a function to do this in VBA. I did a similar function many years ago for allocating shift hours into job function/hours (not in Access).

You could start with the 10 hours, get your first result (3/11) * 10 = (2.73) round it to 3, subtract that from 10 (leaving 7) (and the 2.73 from the 11 leaving 8.27), then run your next one as (3/8.27) * 7 = (2.54) - round to 3, subtract from 7 leaving 4. Result are 3, 3, 4.

Any algorithm you come up with could do some variation on that.
Good luck.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

THe result would be a number with no decimals, the total for the day not to exceed 10.

3,3,4 or 2,2,6 or so

Again, this is not the information that was requested. Somewhere you have a table or maybe two that you are getting data from. It would be helpful to us if you showed us those tables and the information in them and how you want the final results to "look".
[tt]
Table1
Field1 Field2 Field3
data data data
data data data

Table2
Field4 Field5 Field6
data data data
data data data
[/tt]

Now I want to add up all the information in Field 6 where field1 and field4 are either "This" or "That"

[tt]
Field1 Field4 Sum of Field6
"this" something 18
nothing "that" 18
[/tt]

Thanks.