averaging numeric fields

[A. Craig]

this may sound silly, but I am having difficulty getting
the average for four seperate fields in the same record.
I want the average to be entered in a field called ave in
the table and record

 

[fredg]

this may sound silly, but I am having difficulty getting
the average for four seperate fields in the same record.
I want the average to be entered in a field called ave in
the table and record

To arrive at the average of the 4 fields on the same record, add an
unbound control to your form.
Set it's control source to:
=(Nz([Field1]) + Nz([Field2]) + Nz([Field3]) + Nz([Field4])) / 4

This figure is not to be saved to any table.
Whenever you need the information in the future, recalculate it as
above.

 

[Guest]

can I have the average bound to a table?

-----Original Message-----

this may sound silly, but I am having difficulty getting
the average for four seperate fields in the same record.
I want the average to be entered in a field called ave in
the table and record

To arrive at the average of the 4 fields on the same record, add an
unbound control to your form.
Set it's control source to:
=(Nz([Field1]) + Nz([Field2]) + Nz([Field3]) + Nz ([Field4])) / 4

This figure is not to be saved to any table.
Whenever you need the information in the future, recalculate it as
above.
.

 

[Kevin Sprinkel]

Yes, you can, but the practice is strongly discouraged,
since you can display it on a form, use it in a query, or
publish it in a report at any time without duplicating any
data, and without any programming.

If you feel there is a good reason to store the average
redundantly, set the form control source to the field you
wish to store it in, and create a Record BeforeUpdate
event procedure which sets the control to the average as
previously described. To prevent your user from
overwriting it, disable the control so that it displays
but cannot gain the focus.

But I would challenge you to defend why it's necessary.

HTH
Best regards.
Kevin Sprinkel

-----Original Message-----
can I have the average bound to a table?

Set it's control source to:
=(Nz([Field1]) + Nz([Field2]) + Nz([Field3]) + Nz

([Field4])) / 4

 

[fredg]

can I have the average bound to a table?

** snipped **

You can, but shouldn't.
What if one of the fields is changed.
Now the data in the Average field is incorrect.
It wastes memory, and it's slower to fetch the field from the hard drive
than to recompute.
Besides going against the issue of database normalization.

However, if you must....
Assuming there is a Field named [AverageField] in the table and the
control that does the calculation is named [FormControName], then:

Add the [AverageField] field to the form.
Set the control's Locked property to Yes
Code the Form's BeforeUpdate event:
[AverageField] = [FormControlName]