Avg query

[Sandra]

I think I must be doing something wrong. I'm trying to get an average of 5
fields in a query using the following:

Total Average Age:
Avg([averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV])

The result I am getting however is the sum of the 5 fields.

Thanks!

 

[Michel Walsh]

Aggregates operates vertically, across the records, not horizontally, across
the fields.

Right now, you compute the average of an expression. If there is just one
record, that is, indeed, like returning the expression.

You design is probably not normalized, that leads to uselessly complex
expression:

(Nz([averageageClassI], 0)+ Nz([averageageClassII],
0)+Nz([averageageClassII-a],0)+Nz([averageageClassIII],0)+Nz([averageageClassIV],
0))
/
(iif([averageageClassI] Is null, 0, 1) +iif([averageageClassII] Is Null, 0,
1) +iif([averageageClassII-a] Is Null, 0, 1)+iif([averageageClassIII] Is
Null, 0, 1)+iif([averageageClassIV] Is Null, 0, 1))


and that does not cover the case where the five fields all hold a null
value.




Vanderghast, Access MVP

 

[John Spencer]

Avg is a function that averages over rows not within rows.

If your fields all have data in them then to get the average of the five items
you would use

[averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV]/5

Although with a title like AverageAgeClass you may be looking for something
else. What that something else may require you to post more details about
what you are trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Sandra]

No, all 5 of the fields do not necessarily contain data. It might be 6, 0,
0, 3, 5 for example.

The data in the field itself (AverageAgeClassI for example) is supplied by
the agency and we simply data enter it. The number I am trying to come up
with is the average across all classes for the agency, which in the example
above would be 5 (rounded).

I hope that makes sense.

Avg is a function that averages over rows not within rows.

If your fields all have data in them then to get the average of the five items
you would use

[averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV]/5

Although with a title like AverageAgeClass you may be looking for something
else. What that something else may require you to post more details about
what you are trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I think I must be doing something wrong. I'm trying to get an average of 5
fields in a query using the following:

Total Average Age:
Avg([averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV])

The result I am getting however is the sum of the 5 fields.

Thanks!

 

[Michel Walsh]

Zero is data, NULL is absence of data, at least, as commonly expected by
those who developed database tools, like Access.


If you don't want the zeros, then, normalize your data, or if you cannot,
for some reason, then use something like:


([averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV])/ABS([averageageClassI]<>0 +[averageageClassII] <>0 +[averageageClassII-a]<>0 +[averageageClassIII] <> 0 +[averageageClassIV] <> 0)That assumes there is at least one not zero value.Vanderghast, Access MVP"Sandra" <[email protected]> wrote in messagenews:[email protected]...> No, all 5 of the fields do not necessarily contain data. It might be 6,0,> 0, 3, 5 for example.>> The data in the field itself (AverageAgeClassI for example) is supplied by> the agency and we simply data enter it. The number I am trying to come up> with is the average across all classes for the agency, which in theexample> above would be 5 (rounded).>> I hope that makes sense.>> "John Spencer" wrote:>>> Avg is a function that averages over rows not within rows.>>>> If your fields all have data in them then to get the average of the fiveitems>> you would use>>>>[averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV]/5>>>> Although with a title like AverageAgeClass you may be looking forsomething>> else. What that something else may require you to post more detailsabout>> what you are trying to accomplish.>>>> John Spencer>> Access MVP 2002-2005, 2007-2008>> Center for Health Program Development and Management>> University of Maryland Baltimore County>>>> Sandra wrote:>> > I think I must be doing something wrong. I'm trying to get an averageof 5>> > fields in a query using the following:>> >>> > Total Average Age:>> >Avg([averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV])>> >>> > The result I am getting however is the sum of the 5 fields.>> >>> > Thanks!>>

 

[John Spencer]

Zero is a value. I believe you want to ignore the zeroes. An expression
something like the following should work


[averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV]
/ (IIF([averageageClassI]=0,0,1) + IIF([averageageClassII]=0,0,1) +
IIF([averageageClassII-a]=0,0,1)+ IIF([averageageClassIII]=0,0,1) +
IIF([averageageClassIV]=0,0,1))



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

No, all 5 of the fields do not necessarily contain data. It might be 6, 0,
0, 3, 5 for example.

The data in the field itself (AverageAgeClassI for example) is supplied by
the agency and we simply data enter it. The number I am trying to come up
with is the average across all classes for the agency, which in the example
above would be 5 (rounded).

I hope that makes sense.

Avg is a function that averages over rows not within rows.

If your fields all have data in them then to get the average of the five items
you would use

[averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV]/5

Although with a title like AverageAgeClass you may be looking for something
else. What that something else may require you to post more details about
what you are trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I think I must be doing something wrong. I'm trying to get an average of 5
fields in a query using the following:

Total Average Age:
Avg([averageageClassI]+[averageageClassII]+[averageageClassII-a]+[averageageClassIII]+[averageageClassIV])

The result I am getting however is the sum of the 5 fields.

Thanks!