Count values outside of upper/lower limits

[jetersauce]

My query that contains the fields

Result
LowerLimit
UpperLimit

I need a way to calculate the total number of Result values that fall outside of the upper and lower limits. What would be the best way to do this?

 

[Beetle]

Not much to go by there. Can you provide a little more information?

Where are the values you want to compare to the Lower/Upper limits?

In other fields within the same table? In a different table?

What's the context? You want to create a query? You want to do the calculations in code? You want to Count the values? Sum the values?

 

[jetersauce]

The purpose of this query is to have a general query that can calculate the % compliance of different tests. I'm trying to get to the point where I can calculate the expression:

Compliance: ([TotalParams]-[NumberOutOfSpec])/[TotalParams]*100

To calculate the [TotalParams] field I'm using the function

TotalParams: DCount("Param","AFL_Calc_Compliance"))

AFL_Calc_Compliance is the name of this Query

The values for the Results field are contained in a table called Results. The upper/lower limits are contained in a table called CustomerParams. I have these tabled joined in the query.

What I need to figure out is how to calculate the [NumberOutOfSpec] field. This field needs to be the total number of results that fall outside the limits.

Example:

Param       Result    LowerLimit       UpperLimit
Weight 1     241.1     212.63          240.98     <-Out of Spec
Weight 2     233.9     212.63          240.98     <-In Spec
Weight 3     237.8     212.63          240.98     <-In Spec

So with these records [NumberOutOfSpec]=1 and [TotalParams]=3. So the Compliance will be 66.67%

I am also eventually going to need to figure out how to get it to ignore Params with Null values (the number of Params that will contain results vary with the product).

 

[Beetle]

One option would be to use an expression like the following in the first query;

IIf([Result]<[LowerLimit] Or [Result]>{UpperLimit],1,0)

Then in the other query, Sum that calculated field from the first query.

 

[jetersauce]

Thanks, that works great!