Storing the result of a calculated field

[Paul-W]

I am relatively new to access. I am aware that you should never try to save
the result of calculations, you should just save the data and let access
recalculate each time, but this poses a problem for me.

I am setting up an estimating database that stores cost values for various
elements in a table - For example the cost of a fence post, concrete and a
fence panel. I can write the queries required to perform the calculations I
need, and produce the reports to print out the quotations.

My problem arises when I need to go back to a quotation some time after, and
one of the elements that the calculations were based upon has changed. Say
for example concrete has increased in price two weeks after the quotation was
sent out. When I revisit the original quotation, access has recalculated it
based upon the new price of concrete, but I need historic data - I need to be
able to store the original quotation for reference, AND to be able to
continue to create new quotations based upon the updated prices.

Somehow I need to store the result of the calculated fields, so that I can
refer back to them. I can do this by creating an append query that doesn't
allow duplicates in the QuotationID field, but this brings up messages
telling me how many records were not appended and seems rather messy.

Does anyone have an answer to my quandery, I'm sure as I'm new I am missing
something fundamental.

 

[Nikos Yannacopoulos]

Paul,

I am aware that you should never try to save
the result of calculations, you should just save the data and let access
recalculate each time...

This tends to be taken as gospel (and I'm as guilty of often being
dogmatic on this, as the next guy here...), but there are exceptions!
There are indeed occasions when it makes sense to save calculations, for
instance in ERP implementations most calculations are saved because the
amount of data stored makes it inefficient to recalculate masses of
numbers all the time! Of course, this would not be common in Access apps.

That said, your case is not necessarily an exception; under a different
design, you would have a table for raw material costs, storing material
ID, unit price, unit and date effective, and your calculations would
look up the price for a given material on the date of the quotation.
This would keep all the historical data, while no calculations are
saved. Additionally, you would still be able to work out how a total
value was reached.

The "hybrid" solution would be to keep all the historical pricing data
as described above, *and* store the quotation totals also, calculating
them at the time of entry only; in this fashion, you would not have to
undergo complex calculations just to run a report, while you would still
have all the detail, and with a minimal cost of additional storage space
(what's one more Long numeric field per quotation?)

I expect some of the experts here will disagree, and I'm willing to be
convinced!

HTH,
Nikos

 

[Paul-W]

Nikos thank you so much for confirming that I am not insane! I will
experiment with storing "date effective" fields alongside cost information
and see how I get on with it.

For the *hybrid* solution (this is where I show my novice status) what would
be the best way to store the result of calculated fields? Is there a *best
way* to do something that I am not supposed to do?

Many many thanks for your help
Paul.

 

[Nikos Yannacopoulos]

Paul,

The next question that usually comes up is "I have an expression in the
Control Source property of the text box, to calculate the value, but the
value doesn't get saved in the table; how do I do it?".

Well, in case you haven't worked it out yet, you can use a control's
Control Source property to either calculate its value, or bind it to a
table field, but not both! My preferred method is to use the property to
bind the control to the table field, and use some simple VBA code (or
even a simple macro with a SetValue action) to populate the control with
the calculated value. Which ever you choose (code or macro), needs to
fire at an appropriate event of all controls participating in the
expression which calculates the value; for example, assuming your
expression is [txtQuantity] * [txtUnitPrice], you would need your macro
or code to fire at, say, the After Update event of both the qty and
price text boxes, so the value is updated for any change in either.

Hope this is enough to get you going.

Nikos

 

[Paul-W]

That's a great help...many thanks Nikos

Paul