Calculate Fields retained in table?

[tpearo]

I have three fields on a form based on a table that I need to calculate the average of two measurements.
Hole 1 Min: [H1Min]
Hole 1 Max: [H1Max]
Hole 1 Avg: [H1Avg]

So I have a formula in the Avg field that reads:
=([H1Min] + [H1Max]) /2

So when the user inputs the measurement in H1Min and H1Max field the db automatically calculates the avg for them.
This works fine now on the form.

But how do I get the average to record into the underlying table?

Tp

 

[mph1]

Storing a calculated field is not a good idea. It goes against the rules of normalization. Based on the information given, it is not necessary since your result can alwayse be calculated on the fly either in a form or report.

HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[THWatson]

tpearo
I assume that you have a field, called [H1Avg] in the table.

There is more than one way you could do this.

Method One
Make the control source for H1Avg in the form H1Avg.
Using code, put your formula expression on the AfterUpdate event for either H1Max or H1Min, whichever is entered last. The code for the AfterUpdate event would be
Me.H1Avg=(Me.H1Min + Me.H1Max) /2)

Method Two
Make a query based on your table. The first two columns would be H1Min and H1Max. The third column would be H1Avg[H1Min] + [H1Max]) /2
Then base your form on that query.

Tom

 

[THWatson]

tpearo
The coments by mph1 are right. You don't usually to store a calculated value, as they can always be calculated by query or form or report. And I should have added that to my post.

I just made my suggestions to be used in the event that you really wish to store the calculated value.

Tom