I have a table as follows....
Device LotID Result1 Result2 Result3
aaa 1 5 10 15
bbb 1 2 4 6
aaa 2 3 6 9
I need to determine the mean of all results for each LotID, so used
the Row Average function described on the Microsoft Knowledge base,
which gives
Device LotID RAvg
aaa 1 10
bbb 1 4
aaa 2 6
I then have a subsequent aggregate query to work out the mean of the
RAvg's for each device type. What i need is.....
Device OverallMean
aaa 8
bbb 4
I cannot seem to get the aggregate function Avg to work on the second
query. I get an error saying that the Jet Database engine could not
execute the SQL statement because it contains a field that has an
invalid data type.
The RAvg function from the Knowledge base is as follows...
Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() will average all the numeric arguments
passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function
I can get this to work if i change the first query to a 'make table'
query then use the second quey on the made table. Is there a known
problem using aggregate functions on calculated query fields?
