Applying/Spreading numbers over a specific set of (office 2003)

Topic

New Reply

Hi all, I am trying to make changes to a database and cannot formulate in my mind a way to do the following process. The database will have a table with many records associated with an ID field which groups specific records. Within these records is a field for “hours proposed”. In this process, someone will look at these hours which span many records and choose to accept them or cut them. They can be cut by a few hours or by many hours. My problem is that say there are 5 records whose “Hours Proposed” sum up to 35 hours. However, 20 hours are in record #1 and the rest are spread across the remaining four records. If the person reviewing these hours decides to cut 10 hours turning the 35 hours into 25 hours, but needs to spread these hours across the same five records in the exact proportion they were originally proposed. How would one go about recalculating how many hpours goes into the same five records so that they add up to 25.

I know this is a very quick and crude explanation of what needs to happen, but I am hoping to get started in the right direction.
Thank You in advance

Reply | Quote

Replies

In your example, you’d multiply each amount by 25/35, so the 20 hours become 20 * 25/35 ~ 14.3 hours.
If the new values need to be whole hours, you’d have to specify how you want to round off the numbers – rounding them all to the nearest whole number might not result in a total of 25 hours. You’d probably need code to adjust the numbers.

Reply | Quote

Thanks Hans, That makes a lot of sense. I presume I can make that calculation either through an update query or by stepping through the records one at a time. My first choice would be the update query. Also if the cuts should all happen on the first records or the last record, is there a way to remove all the hours from say the first reord until it is reduced to 0 then move to the next record if there are any more left to cut? Example: Of those original 35 hour over the five records were cut to 25 hours but the cuts were applied in record order starting with the first, and say the first record had 3 hours and the second record had 10 hours, I would need to apply 3 of the 10 hour cut to the first record and 7 of the 10 hour cut to the second record. In this example, would I be better looping through the recordset using a variable to keep track of the number of hours I am reducing each record by until all 10 hours being cut are used up?

That is a little different from the first question and I am trying to figure out if there is a one fit calculatio/procedure that would apply to both situations, or I would need to set up a choice button on a form to to direct the cuts to happen in each scenario? Sorry for being so general in the example, but I am not sure yet how I am going to implement this in a user friendly way.

Thanks
Kevin

Reply | Quote

For strictly proportional cuts, you can use an update query. For example, asking the user to enter the number of hours to cut:

UPDATE MyTable SET MyTable.Hours = [Hours]*(DSum(“Hours”,”MyTable”)-[Enter number of hours to cut])/DSum(“Hours”,”MyTable”);

If you want to distribute the cut over the first or last records, you should use VBA code to loop through the records; that way you can keep track of how many hours have been taken care of and how many remain to be processed.

Reply | Quote

Hans, Thanks again.

Reply | Quote