Nested Queries

  • Thread starter Thread starter BRich
  • Start date Start date
B

BRich

Greetings,

Two questions (See below for details): 1. Does anyone know a source for a
'nested queries' white papter or knowledge base article that I can access?
I had one and can't find it!
2. Any advice on how to accomplish this? Any good references on this
topic?

I have an application where I want to report average values on sets of
analytical records. I need to weed-out any 'zero' values so they don't mess
up my statistics. (A zero value usually means the sample was not analyzed
for that property. )

The data set looks like the following.
Samples ==> Analyses ==> Properties
A sample can have multiple records in Analyses
An analysis record contains multple Properties (fields in the Analysis
Table).
The value of the field Properties(1) can be zero or non-zero. If it's zero,
I don't want to include it in my results.

I need a query to:
Select all of the records for a Sample, and report back the average value of
each property. The average can contain no 'zero' values for the set of
property1 values, property2 values, prop3 values, etc.

I had a reference article on nested queries that I believe handled this type
of problem, where the query contained other queries nested as parameters. I
suspect I could do the selection of non-zero properties in the nested query,
with the main select query returning the average value... But I can't recall
how to do that.

Cheers,
BRich
 
It might help to see the SQL you are using.

One suggestion is to use an immediate if in your query to return NULL if the
value is zero. UNTESTED SQL statement follows

SELECT FieldA,
Avg(IIF(FieldB=0,Null,FieldB)) as AvgWithoutZero
FROM YourTable
GROUP By FieldA

Optionally, test the field to see if it is not Zero and sum the results of the
comparison. If the field is zero, the test will return zero, if it is not
zero the test will return negative one (in Access Jet) or positive one in some
other databases. You can use the Abs (absolute value) function to return a
positive sum.

SELECT FieldA,
Sum(FieldB)/ Abs(Sum(FieldB<>0)) as AvgWithoutZero
FROM YourTable
GROUP By FieldA
 
Back
Top