Best way to calculate values that depend on historic values?

[kagard]

Greetings:

I'm putting together a database for a contractor. They bill their
clients monthly on projects that may last a year or two. Each project
has a budget and each month's billing items are charged to specific
lines in the budget. Each line item also carries retainage - a
percentage that is due but withheld by the client to assure completion
of the project. This retainage percentage can vary from line item to
line item and from month to month. Here's the problem:

I have to show total retainage on each new bill. Since I'm not storing
calculated values I essentially have to recalculate the entire billing
history from the start of the project to the current date in order to
show retainage to date. That is: Sum(ValueOfWorkDone *
LineItemRetainagePercentage) for all activity less than the current
bill date. Is there a more efficient way to do this?

I thought about storing the results of the calculation but that seemed
to raise as many questions as it solved. For example what if the
contractor has to make an adjustment to an earlier bill? Then I'd have
to write code to update every subsequent bill to keep them in sync.

Any suggestions? TIA

Keithy

 

[Allen Browne]

It seems to me that you have 2 separate entities here:
- the budget (projected estimates agreed), and
- the actual (invoices each month.)

There are cases where these 2 things could validly be different, e.g. The
actual can vary from the budget, or a project may be terminated before
completion, or there could be additional items that were not part of the
original budget.

If that's accurate, then you need to store both separately.

 

[kagard]

Thanks Allen

I am storing the budget and actual in separate tables. The retainage
caclculation is done from the actuals. That's the one I'm struggling
with.

Keith

 

[Allen Browne]

Not sure I've followed what you are doing from the quick summary.

In the actual table(s), you need to record a percentage-to-add-to-the-item
later? If this percentage is known at the time you create the entry for the
Actual, could you do that with an additional field of type Double (format
Percent)?

Then at the end of the project, you could generate the final payment that is
based on those percents of the line items, as the final payment.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

: Thanks Allen
:
: I am storing the budget and actual in separate tables. The retainage
: caclculation is done from the actuals. That's the one I'm struggling
: with.