Adding calculated values to a table

[Joe]

Is there a way to add a field to a TABLE, not a QUERY,
that is calculated from values in other fields of the same
TABLE?? Everything I have found so far deals with adding
calculated fields to query tables , but not data input
tables.

Thanks, Joe 20030823

 

[Ken Snell]

No, because this is not appropriate relational database design. Normally,
one calculates values in a query so that the data do not become obsolete
when you change one of the original data values that was used to calculate
the stored value in the table.

If you really want to store a calculated value in a table for good reasons,
then use a form or a query to handle the calculation and store it that way.

 

[John Vinson]

Is there a way to add a field to a TABLE, not a QUERY,
that is calculated from values in other fields of the same
TABLE?? Everything I have found so far deals with adding
calculated fields to query tables , but not data input
tables.

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.

It can be done, using VBA code on a Form. It cannot be done in a
Table, and it's very, very rare that it should be done in any case.

 

[GPO]

Agree with every thing John said, except that there are certain
circumstances where having a calculated/derived item is necessary for
performance reasons. You would need a situation where:
1. You have extremely complex calculations AND
2. You need to access the end result quickly AND
3. You need it very frequently AND
4. You can be sure that the underlying data is not going to change unless
the calculation is redone.

We fund hospitals using a model more complicated than middle eastern
politics. I use Access as an (extremely cost effective) reporting tool. The
data I use is static and has has the funding calculation already done.
Whilst I ditch most of the derived items for the reasons outlined, I keep
this one. It's not common, but can happen.

GPO

 

[John Vinson]

Agree with every thing John said, except that there are certain
circumstances where having a calculated/derived item is necessary for
performance reasons. You would need a situation where:
1. You have extremely complex calculations AND
2. You need to access the end result quickly AND
3. You need it very frequently AND
4. You can be sure that the underlying data is not going to change unless
the calculation is redone.

<applause> Well said!

Exactly; these are exactly the conditions in which I apply the line in
my consulting brochure: "Judicious denormalization, only when
necessary".