Calculated Fields

[joseph]

I have been reviewing comments on the Access groups since I am new to
database creation and wanted to find a few pointers. (Forgive my
terminology, or lack of it.)

I have seen several comments about storing data in tables at the "lowest
level". That makes sense to me. But I have a question about why a
calculation would not be considered a value at that level and not allow a
calculated field in a table. I guess I do not see how that would conflict
with "good database design".

For example, in an Inventory table, why would the "Issue Cost" of an item
not be considered as a value to be stored in the table? It is certainly
unique for that inventory item, just like the Item Number would be. The only
difference I see is that it is based on other values for that item.

Many of the comments in the group have been to always recalculate the value
any time it is needed. It seems to me that doing a calculation once and
storing the value would be far more efficient than doing that calculation
many times over in various reports, etc.

I have seen some comments about making sure the calculated value is correct.
If the supporting fields change, the calculated result could change, similar
to a spreadsheet formula.

Am I missing something or am I just so new to creating a database that I
need to learn more rules?

 

[Jeff Boyce]

Joseph

Good questions! (keep asking)

I don't know that I'd go so far as to call it a rule, but there are, as you
may have found, some very good reasons not (generally) to store calculated
values.

In my opinion, the strongest argument against storing calc'd values is that
there is no way, within the Access/JET table, to recalc when one of the
components change. Excel can do this, but only in one direction -- that is,
if the underlying components change, the Excel-calc'd value changes. But
what if the cell holding the calc'd value is altered? Does Excel go back
and modify the components?!

You will have to write procedures to reconsider both the calc'd value and
the components and get input from the user about what alterations will be
allowed if you want to control this.

In the example you gave, I'm not sure I have the same definitions you do.
The price of an item of inventory doesn't seem to me to have any dependency
on how many are sold. The "extended price" of an item sold would seem to be
the calculation of unit price times quantity. If you have both of these
components, you don't need to store extended price!

Other reasons given for not storing calc'd values include the fact that it
eats extra hard disk space (given prices, not a major concern), and that
calculations in RAM are considerably faster than disk access (could be
important).

Or have I misunderstood your points?

Good luck

Jeff Boyce
<Access MVP>

 

[John Vinson]

Many of the comments in the group have been to always recalculate the value
any time it is needed. It seems to me that doing a calculation once and
storing the value would be far more efficient than doing that calculation
many times over in various reports, etc.

I have seen some comments about making sure the calculated value is correct.
If the supporting fields change, the calculated result could change, similar
to a spreadsheet formula.

This is exactly and precisely what all of us here recommend: it's just
that it is not necessary to store the calculated field in a *table* in
order to accomplish this. Just do the calculation in a Query. Here's
my "boilerplate" text on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you're worried about the time and expense of doing a calculation -
bear in mind that computers are very, very good at doing calculations,
very quickly. You can calculate thousands of additions during the time
that a disk spins once on its axis; if you're worried about time, then
you want to do as much as possible by calculation, and as little as
possible by retrieving data from disk!

John W. Vinson[MVP]

 

[joseph]

My definition of "Issue Cost" is the Purchase Cost divided by various
conversions to get to a "usable unit cost". For example, I buy screws by a
box of 100 costing $200, but use them by the each, so my "Issue Cost" is
$200/100 giving $2. If this $2 value were stored, there would be no need to
repeat the "Issue Cost" caluclation each time I wanted to compute any sort of
an "extended cost", say for a work order.

You speak of "one direction" in changing the components. I do not see
how/why it could go any direction but from the components to the end result.
I cannot see any way for any program (spreadsheet or database) to determine
which component(s) needs to be changed if the end result were somehow changed.

I do agree that there could be some integrity issues with the calculated
field. From what I understand Access (and presumably other database
programs), with the use of forms and/or queries, this would be eliminated.

I am by far not a computer expert, but it seems to me that bringing in
(potentially)many fields from a table, then do a calculation on those fields,
would not be as efficient as simply bringing in one field would be. (Why use
a shovel if all you need is a teaspoon?)

Your comments are making it sound like not having a calculated field in a
table is more of an Access issue than a "database design" concept issue.
Considering I am stuck with Access, I may just plain have to deal with it.

Thanks for the feedback.

 

[Rick Brandt]

My definition of "Issue Cost" is the Purchase Cost divided by various
conversions to get to a "usable unit cost". For example, I buy
screws by a box of 100 costing $200, but use them by the each, so my
"Issue Cost" is $200/100 giving $2. If this $2 value were stored,
there would be no need to repeat the "Issue Cost" caluclation each
time I wanted to compute any sort of an "extended cost", say for a
work order.

Generally when the discussion in these groups is "calculated fields" it is
referring to storing a calculation that can be derived from other fields in
the same row of the same table. For example...

UnitPrice Qty ExtendedPrice

In the above there is no good reason to store ExtendedPrice in the table as
one can always (and easily) obtain that value using (UnitPrice * Qty).

Now, when you are talking about calculations involving data in *other*
tables then the rule still applies, but it is definitely not as hard and
fast as the situation described above. There are cases where performing a
calculation that involves aggregating and/or multiple tables where storing
the result is done because performing the calculation on-the-fly incurs too
much of a performance penalty. Those decision have to made on a case by
case basis.

 

[Craig Alexander Morrison]

<<< have seen some comments about making sure the calculated value is
correct.
If the supporting fields change, the calculated result could change,
similar
to a spreadsheet formula. >>>

Just to deal with this point, first.

Using SQL Server you can create triggers that will execute a stored
procedure to keep the database consistent. IOW whenever the fields that are
part of a calculated field are changed the calculation is invoked to correct
the value in the calculated field.

If you use Jet you have to keep this processing on the application end, the
database cannot enforce the calculated result to be consistent. As long as
you can ensure that only the application that has this logic will be run
against the Jet database you will get away with it.

Having said all that you should not, even in SQLS (or DB2 or Oracle) store a
calculated field made up from other fields in the same record.

You should also be careful to distinguish between a calculated field that
will be dependant on other values for all time and one that is calculated
but then has a life of its own. Strictly it has been derived from other
fields but once calculated remains constant irrespective of changes in the
other fields.

An example is pricing information for an order remains as agreed, yet the
price for the product may be subject to fluctuations for any subsequent
order.

 

[Craig Alexander Morrison]

It is a rule.

However, If database consistency can be managed by the database manager so
that no application can violate it then it can be done, Codd and Date accept
that. You would need to use SQL Server or the like as Jet cannot enforce
consistency, it is at the mercy of the bad application and direct table
access.