Partial Update of Fields in a record

Topic

New Reply

I have a database containing say ten fields. Four out of these are filled with data input by users. I want to do some calculations with user supplied data and put in calculated values in other six fields. Is it possible to use Append or Update queries for this purpose.

Reply | Quote

Replies

You certainly could use an update query to update your table with calculated data. However, a better method would be to have a query display the calculations with the data. Your reports could then run off this query and your table size is greatly reduced. Plus anytime you view your data using the query with calculated fields the calculations are up-to-date and accurate. This may not be the case when you have to run an update query in order to change the calculated amounts in your table.

Reply | Quote

It is considered poor design to put calculated values into tables. The exception to this is so-called [indent]

---

snapshots

---

[/indent], which are records that contain things like calculated balance as of a particular date. These are used frequently in accounting, sales and inventory applications. However, these records are not updated. Instead a new record is added periodically with an updated balance.

Reply | Quote

The others are correct in that it is almost never desirable to have redundant information in your tables. However, I have broken that rule a few times when it was desirable to have some sort of concatenation of fields stored as a separate field, e.g. as a key (and yes, I know about autonumbers as well as combined primary keys – explaining myself would take a long message).

Anyway, you would use update queries if you wanted to set those fields after the fact. If this is something you are presently setting up, I would suggest using code in your forms (in the After Update event code of the relevant fields that the user is entering) to store the “calculated” values.

Reply | Quote