Average for multiple fields in a query 1

[fishtek]

I have a query with multiple fields containing weight data - Weight1, Weight2, Weight3, etc.. I would like to average the fields (not rows) and ignore fields with null values in the average calculation. I'm sure its obvious but I can't make it work in the query. Thanks for any help.....

 

[Remou]

It is best to post your SQL. It may be best to create a Union query to normalize your table, and use that for the average. If you are addind fields, remember you must use Nz to eliminate nulls.

 

[BigRed1212]

What Remou said, but also post some sample data so we can see the structure.

 

[PHV]

I've already posted this function:

'A generic function to get the average value of an arbirtrary numbers of numeric values:
Public Function myAvg(ParamArray Args())
Dim i As Long, N As Long, rv
For i = 0 To UBound(Args)
  If IsNumeric(Args(i)) Then rv = rv + Args(i): N = N + 1
Next
If N > 0 Then myAvg = rv / N
End Function

In your SQL code you may use this:
SELECT myAvg(Weight1, Weight2, Weight3) As Average
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fishtek]

Thanks PHV..the function does exactly what I need....