Distribute numbers evenly across records

[mcneilkm]

Hi all,

I am a newby to this forum. I am making modifications to an existing database. The structure is basicall one primary record in a main table with many associated records in a second table. The associated records has a field in it called hours and is formated as a number. I need to update this field based on criteria in a text box which is also a number. In all the the idea is to take the text box number which represent cuts to the hours field but these cuts need to be spread over all of the records in the same ratio as the original hours. Here goes... example: 5 records have a total of 21 hours. each record obviously hs a different "hours" number. Record 1 has 3; record 2 has 1; etc until all five add to 21. If I wanted to cut the total of 21 hours by 11 hours I have the following formula in an update query: 11divided by 21 * the number of hours inf the hours field. It works great until I have to round the numbers. Sometimes (more often than not) the calculation does not work out evenly and I always end up not getting to the new number, (21 to 10). Every rounding combination that I have tried does not tie up the left over fraction of numbers. Hopefully I have explained this enough for someone to understand. Please if anyone has any ideas, please let me know how to control the calulation so that I end up with spreading these "cut hours" across all the records in the same percentage of the original.

Thanks
Kevin

[Jim Doherty]

Hi all,

I am a newby to this forum. I am making modifications to an existing database. The structure is basicall one primary record in a main table with many associated records in a second table. The associated records has a field in it called hours and is formated as a number. I need to update this field based on criteria in a text box which is also a number. In all the the idea is to take the text box number which represent cuts to the hours field but these cuts need to be spread over all of the records in the same ratio as the original hours. Here goes... example: 5 records have a total of 21 hours. each record obviously hs a different "hours" number. Record 1 has 3; record 2 has 1; etc until all five add to 21. If I wanted to cut the total of 21 hours by 11 hours I have the following formula in an update query: 11divided by 21 * the number of hours inf the hours field. It works great until I have to round the numbers. Sometimes (more often than not) the calculation does not work out evenly and I always end up not getting to the new number, (21 to 10). Every rounding combination that I have tried does not tie up the left over fraction of numbers. Hopefully I have explained this enough for someone to understand. Please if anyone has any ideas, please let me know how to control the calulation so that I end up with spreading these "cut hours" across all the records in the same percentage of the original.

Thanks
Kevin

Kevin

What is the data type of the number you are working with? look at the DECIMAL datatype which will give you the precison you seek. In addition you might want to look at creating a formula in a 'query' as a column so that the return values can be 'calculated' rather than stored?

Jim