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