Calculation query, how to avoid extreme values?

[Mikael Lindqvist]

Good morning,

I have 1 table with a few fields (A, B, C) that I'm using for various
calculations.

For various reasons are some of the input "weird" and I want to leave that
out of the calculation (average).

For example, Field A requires > 0 AND < 100, B < 400

Now, if one record doesn't fullfil A requirement I don't want to use it for
A's average calculation BUT I want to use it for B's average calculation (if
it satisfy B's requirement).

This means that if I use the query criteria A: >0 AND <100 it will filter
out all other records and thus I can't use them for B's average calclulation
(which I want).

Does anyone know how to get around this?

Kindly,
Mikael
Sweden

 

[Stefan Hoffmann]

hi Mikael,

For various reasons are some of the input "weird" and I want to leave that
out of the calculation (average).

For example, Field A requires > 0 AND < 100, B < 400

Now, if one record doesn't fullfil A requirement I don't want to use it for
A's average calculation BUT I want to use it for B's average calculation (if
it satisfy B's requirement).

The only thing you can do: use sub-queries, e.g.

SELECT
Count(*),
Avg(A),
Avg(B),
(SELECT Avg(A) FROM

WHERE A > 0 AND A < 100) AS A1,
(SELECT Avg(B) FROM

WHERE B < 400) AS B1
FROM

mfG
--> stefan <--

 

[John Spencer]

You can use an expression and calculate the average based on the expression.

SELECT Avg(IIF [A]>0,[A],Null) as AverageA
, Avg(IIF <400, ,Null) as AverageB
FROM [YourTable]

If you are using the design view to construct the query, that would look
something like
Field: IIF([A]>0,[A],Null)
Total: Avg
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..