Aggregate Query problem

sausage31

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?

Nov 13 '05 #1

John Winterbottom

"sausage31" <si**********@dynexsemi.com> wrote in message
news:de**************************@posting.google.c om...

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

select LotID, avg((Result1 + Result2 + Result3 ... + ResultN) / N) as
overallMean
from foo
group by LotID

Nov 13 '05 #2