calculated fields

[chip Goodaker]

I want to add a calculated field to a table. In Excel I
simply write a function: (i.e. add three columns and
write the result in the fourth). Apparently it's not
that easy in Access. I can make a query and get the
result, but I can't get the result to appear within the
table with which I am working. This is an Inventory
database and the table is the main product table. The
calculation is to add an items beginning inventory plus
deliveries and subtract sales. The result is the
calculated Qty on Hand.

Help, chip

 

[Douglas J. Steele]

Calculated fields should rarely, if ever, be stored in tables. If you can
calculate the value in the query, just use the query wherever you would
otherwise have used the table.

 

[John Vinson]

I can make a query and get the
result, but I can't get the result to appear within the
table with which I am working.

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.

This is true even for an inventory application in all but the most
unusual cases.