Updating muli-records from calculated fields on a form.

[Guest]

I need some help on how you update a table from a form showing multple
records by scrolling thru them. The form has fields that are calculated from
the records. Ex. I have a form that shows 6 locations (6 records) employee
counts. There is a field that calculates the average number of employees per
location. This field will be on every record. ...How would you update the
table?

 

[John W. Vinson]

I need some help on how you update a table from a form showing multple
records by scrolling thru them. The form has fields that are calculated from
the records. Ex. I have a form that shows 6 locations (6 records) employee
counts. There is a field that calculates the average number of employees per
location. This field will be on every record. ...How would you update the
table?

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.


John W. Vinson [MVP]

 

[Guest]

John,

You may have misunderstood. I saving daily stats on a project. The data is
saved to a log file. Tha data will then be used to put out weekkly reports.
Can you help me out on this

 

[John W. Vinson]

John,

You may have misunderstood. I saving daily stats on a project. The data is
saved to a log file. Tha data will then be used to put out weekkly reports.
Can you help me out on this

My argument would be the same. If you have the data from which the stats are
calculated, you can calculate them today, or next week, or next January! The
reports can be based on Queries containing the calculations. Are you assuming
that data must be stored in a table in order to generate a report?

You can - if you choose to do so - violate the rules and store the calculated
fields; one way is to have six controls on the form bound to the calculation
expressions, and six more controls (maybe invisible) bound to the table
fields. In the form's BeforeUpdate event (or some other suitable event) you
can copy the contents of the calculated fields into the bound controls. It's
still a bad idea and not necessary.

John W. Vinson [MVP]

 

[Guest]

Thanks for your help .. well relook at the process..