figuring averages in Access

[loyal2him]

I am trying to take an average of five fields in a record and place the
result in a sixth field. I have tried various methods including DAvg
and AVG, but to no avail. The problem is that it needs to ignore null
fields. For instance, if Field1 has a value of 10, Field2 has a value
of 10, Field3 has a value of 10, Field4 and Field5 are null, the
average needs to be equivalent to (10 + 10 + 10)/3 NOT (10 + 10 +
10)/5. Any ideas?

I am trying to code this into a button on a form, but if it ends up
being run in a query, i am fine with that, it just needs to get done.

I tried AVG(Field1 + Field2 + Field3 + Field4 + Field5) in a query, but
it didn't give me an average, just a total.

 

[loyal2him]

BTW: I am pretty much a newbie when it comes to all this, so go easy on
me. Thanks!

 

[Douglas J. Steele]

At least part of your problem is caused by the fact that tables in
relational databases should never have multiple fields like you're
describing: it's called a repeating group, and it's a violation of database
normalization principles.

If you had multiple rows, rather than multiple fields, your calculation
would be simpler.

Having said that, if you cannot (or will not) correct your design, try:

IIf((IsNull([Field1], 0, 1) +IsNull([Field2], 0, 1) + IsNull([Field3], 0, 1)
+ IsNull([Field4], 0, 1) + IsNull([Field5], 0, 1)) > 0,
(Nz([Field1],0) + Nz([Field2], 0) + Nz([Field3], 0) + Nz([Field4], 0) +
Nz([Field5], 0) / (IsNull([Field1], 0, 1) +IsNull([Field2], 0, 1) +
IsNull([Field3], 0, 1) + IsNull([Field4], 0, 1) + IsNull([Field5], 0, 1)),
0)

(IsNull([Field1], 0, 1) +IsNull([Field2], 0, 1) + IsNull([Field3], 0, 1) +
IsNull([Field4], 0, 1) + IsNull([Field5], 0, 1)) will tell you how many of
the fields are non-null.