averaging numeric fields

A

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
 
F

fredg

A. Craig said:
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.
 
G

Guest

can I have the average bound to a table?
-----Original Message-----
A. Craig said:
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.
.
 
K

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
 
F

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top