Calculated Fields

[l martin]

I am new to Access, so I have spent some time in the
Access groups reading. I have read several messages that
mention the concept to store data at the "lowest" level
possible. That makes sense to me, but I have a question.

Why does Access not allow a calculated field to be defined
in a table? If the calculation is based on the fields in
the record, the calculation is still at the "lowest"
level, so why not store it in the table?

It seems to me that storing a value is much more efficient
than having to calculate it in a report or form every time
that report or form is used.

I have used several database programs over the years and
every one allows calculated fields in the tables/files.
Access is the only one I have had contact with that does
not.

Thanks.

 

[Tim Ferguson]

Why does Access not allow a calculated field to be defined
in a table? If the calculation is based on the fields in
the record, the calculation is still at the "lowest"
level, so why not store it in the table?

Because it will be wrong! As soon as one of the source fields is changes,
the derived value will go out of date.

It seems to me that storing a value is much more efficient
than having to calculate it in a report or form every time
that report or form is used.

Wrong: for the huge majority of calculations waiting for a hard disk to
crank round and chug another page into memory is much slower than doing the
multiplication or addition or whatever. But the real reason is the first
one above. Calculating a new value when it is required means that always
agrees with the source.

I have used several database programs over the years and
every one allows calculated fields in the tables/files.
Access is the only one I have had contact with that does
not.

Then Access is the first database program you have used. The point of a
database manager is to protect your data and prevent inconsistencies. The
schema design is an important part of that -- which is why we pay so much
attention to R theory and Normal Forms and so on -- and so a respectable db
engine should not encourage you do things that will undermine the itegrity
of your own data. Paradox, dBase are emphatically not database managers.

Note that SQL Server appears to allow you to define calculated columns, but
actually these are "virtual" and not stored anywhere.

Hope that helps


Tim F

 

[John Vinson]

It seems to me that storing a value is much more efficient
than having to calculate it in a report or form every time
that report or form is used.

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.

I have used several database programs over the years and
every one allows calculated fields in the tables/files.
Access is the only one I have had contact with that does
not.

You are mistaken; Access does allow calculations to be done - but done
*IN QUERIES*, not in table datasheets. A Paradox or FileMaker Pro
"table" with calculated fields is (under the covers) simply a query.
It's just a different way of presenting the calculation.