Calculating Averages

[rdscott]

Access 2003

I have several fields in a table that I want to sum, get an average and update the avg value in another field. This is for each record. If anyone can help, what is the best way of doing this? I tried using the avg function, but it averages the whole column.

[Killer42]

I have several fields in a table that I want to sum, get an average and update the avg value in another field. This is for each record. If anyone can help, what is the best way of doing this? I tried using the avg function, but it averages the whole column.

At the simplest level, you could do an update query to update FieldX to ([Field1]+[Field2]+[Field3]/3).

[rdscott]

thanks, but how would I eliminate the fields that have zero values. e.g. in your example if field 3 has a zero value, i want just field 1 and field 2 values divided by 2

[Killer42]

thanks, but how would I eliminate the fields that have zero values. e.g. in your example if field 3 has a zero value, i want just field 1 and field 2 values divided by 2

Perhaps it would be simplest to create a VB function which does the calculation, then use that function in your SQL.

For example (I forget how to write an UPDATE query)...

SELECT fAverageOf3([Fld1], [Fld2], [Fld3]) As FieldX FROM [TABLE];

Then in a VBA module, create a public function something like this (again, this is just off the top of my head)...

Public Function fAverageOf3(ByVal Val1 As Single, ByVal Val2 As Single, ByVal Val3 As Single) As Single
Dim Count As Long
Count = 0 - (Val1 <> 0) - (Val2 <> 0) - (Val3 <> 0)
If Count Then
  fAverageOf3 = (Val1 + Val2 + Val3) / Count
End If
End Function

Note that True is represented by -1, hence the subtractions in calculating Count.

I expect there are ways this approach could be made more generally applicable, rather than depending on a set type and number of fields (3 Single in this example).