Incorporating subquery into A query by SELECT

[Benton]

I'd like to incorporate this SQL statement into my main query as a
subquery:Here's the statement. (Select Avg(Average) fromQAVERAGE).With this
statement i'd like to create a field on my QBE grid that would calculate the
average of the below fields(i.e 200407,200408....) Is this gonna work

SELECT DISTINCT Health_Prod1.Broker, Health_Prod1.EffMonth,
Sum(Health_Prod1.[200407]) AS SumOf200407, Sum(Health_Prod1.[200408]) AS
SumOf200408, Sum(Health_Prod1.[200409]) AS SumOf200409,
Sum(Health_Prod1.[200410]) AS SumOf200410
FROM Health_Prod1
GROUP BY Health_Prod1.Broker, Health_Prod1.EffMonth;

Here's a sample of my query results
Broker EffMonth 200407 200408 200409 200410 average
100 01/09/2003 0 0 0
294 01/07/2004 6984 0 0 0
100 01/09/2004 0 0 7164 0
100 01/10/2004 0 0 0 348

 

[Michel Walsh]

Hi,


SELECT
...
( Nz([200407], 0) + Nz([200408], 0) + Nz([200409], 0) + Nz([200410], 0) )
AS Numerator,
(4+ IsNull([200407]) + IsNull([200408]) + IsNull([200409]) +
IsNull([200410]) AS Denominator
Numerator / iif(( 0=Denominator, Null, Denominator) AS myAverage
....



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,

missing a coma.


( Nz([200407], 0) + Nz([200408], 0) + Nz([200409], 0) + Nz([200410], 0) )
AS Numerator,
(4+ IsNull([200407]) + IsNull([200408]) + IsNull([200409]) +
IsNull([200410]) AS Denominator ,
Numerator / iif(( 0=Denominator, Null, Denominator) AS myAverage


I assumed you used Jet.

Vanderghast, Access MVP