Performing an Average calculation in a query

[Techfi]

I am going to start this off by saying that I have not used Access for about 2 years, so I have forgotten much about things like this.

I have a query, and I need to have it calculate the average of 5 fields in a record, and show that as another field in the query.

 

[Corticus]

Hi Techfi:

Well you can't really use an average()-like function, since you can't specify a range of fields, so you have to just do the average manually.

Start a query and add the necessary fields, and then in design view, under where it says Field, add something like:
Average: ([Field1] + [Field2] + [Field3] + [Field4] + [Field5])/5

Replace Fieldx with your field names.

Hope this helps,

 

[bat17]

NewField:=([1stField]+[2ndField]+[3rdField]+[4thField]+[5thField])/5
should do it. You may need to look at what happens if one of the fields has no data though.

HTH

Peter

 

[Techfi]

do you know how to make it account for empty records in the calculation? Ideally, if 3 out of the 5 fields have a value, then I want the average calculation to divide by 3 instead of 5. Any suggestions?

 

[bat17]

NewField: (nz([field2])+nz([field3]))/((IsNull([field2])+1)+(IsNull([field3])+1))

You need to use some thing like this, The NZ() replaces Nulls with 0's and the IsNull()+1 lets you count how many of the fields have data.
However this is still not a complete answer as if all fields are Null it will giv an error dur to Div by 0.

HTH

Peter

 

[linger]

hi..

i was looking at your question that's really similar to mine. i was wondering how should i change the expression if all my fields are null or zero so that it does not return the div by 0 error?

thanks
ling

 

[bat17]

You need to wrap it in another level of iif's to test if it is all null and then return a null.

HTH

Peter

 

[linger]

so all i have to do is put another expression that returns a null if all the input values are null?

if that's the case then thanks a million!

yiling