472,804 Members | 1,605 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,804 software developers and data experts.

Aggregate Query problem

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
1 2183
"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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
2
by: Toby Dann | last post by:
I have an aggregate query as recordsource for a form to show a list of invoices, including the totals (calculated by the aggregate query - I'm trying to duplicate as little info as possible here)....
5
by: Jim | last post by:
Need help with aggregate function...for each unique product, I need the provider with the cheapest cost factor Here't the table (Table1) ID product provider factor 1 123456 abc...
10
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
12
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id,...
0
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
5
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
1
by: codeWarrior007 | last post by:
Hi, i'm running a query and everytime I run the query i get the error: 'Aggregate Type' is not a valid name. Make sure that it does not include invalid characters, punctuation, or is not too...
3
by: ncsthbell | last post by:
I am pulling my hair out on this! Seems like it should be easy, I just can not get it to work like I want. I know I am doing something wrong, so I hope someone can be so kind to guide me!!! I...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.