figuring averages in Access

L

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.
 
L

loyal2him

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

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.
 

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

Top