Mean of multiple fields that may contain nulls

[Skip Albertson]

I have a complicated chemical result that I have calculated three
different ways in a query from raw data contained in a database (each is
a time series). I would like to make a new query of the first query that
calculates the means of the three estimations contained in the first
query (as a function of time), but some of the calculated fields are
nulls. I don't think I can use Avg or DAvg. I suspect I have to use sum
and count. Which version of count omits nulls and if I divide sum/count,
how do I avoid an error if count=0 (i.e., all are nulls)?

Mean: Sum([Field1]+[Field2])/(Count([Field1])+Count([Field2]))

only returns the correct answer when both fields are not null.

Mean: (Sum([Field1])+Sum([Field2])/(Count([Field1])+Count([Field2]))

is no different!!!

Thanks in advance for your help.

(e-mail address removed)

 

[Jeff Boyce]

Skip

That will depend on whether your "nulls" mean no measurement was made, in
which case your average would include fewer fields, or the field value is
"0" (like the count of coins in my pocket right now).

If you are sure you have Nulls, and don't wish them included in your
calculation, exclude them from the query that does the calculation by using
"Is Not Null" as a selection criterion. Then your counts and sums will only
use values.

If you want to include all potential measurements, including Nulls, use the
Nz() function (NullToZero) to convert Nulls to a value that Count and Sum
can use, say "0"!

Good luck!

Jeff Boyce
<Access MVP>

 

[Skip Albertson]

Hi Jeff,

That still doesn't seem to work. Here is a simple example of my problem.
Suppose I have a table with 4 entries:

Date Estimate1 Estimate2
1/1/2003 5 null
1/2/2003 4 6
1/3/2003 0 10
1/4/2003 null 5

The result I would like to achieve in a query or nested queries is:

Date Result
1/1/2003 5
1/2/2003 5
1/3/2003 5
1/4/2003 5

Count([estimate1])+count([estimate2]) returns:
1
2
2
1

Sum([estimate1])+count([estimate2]) returns:

Null
10
10
Null

So the closest I can get is
Date Result
1/1/2003 null
1/2/2003 5
1/3/2003 5
1/4/2003 null

Is there a way to design a query using Sum that will return:
10
10
10
10

???

If I use "is not null" I only get two records:

10
10

I wish 'sum' worked like 'count'.

Thanks for your help!

Skip

 

[Skip Albertson]

I've got an answer!

Sum:
(IIf(IsNull([Estimate1]),0,[Estimate1])+IIf(IsNull([Estimate2]),0,[Estimate2]))

Thanks to everyone for the help.

Hi Jeff,

That still doesn't seem to work. Here is a simple example of my problem.
Suppose I have a table with 4 entries:

Date Estimate1 Estimate2
1/1/2003 5 null
1/2/2003 4 6
1/3/2003 0 10
1/4/2003 null 5

The result I would like to achieve in a query or nested queries is:

Date Result
1/1/2003 5
1/2/2003 5
1/3/2003 5
1/4/2003 5

Count([estimate1])+count([estimate2]) returns:
1
2
2
1

Sum([estimate1])+count([estimate2]) returns:

Null
10
10
Null

So the closest I can get is
Date Result
1/1/2003 null
1/2/2003 5
1/3/2003 5
1/4/2003 null

Is there a way to design a query using Sum that will return:
10
10
10
10

???

If I use "is not null" I only get two records:

10
10

I wish 'sum' worked like 'count'.

Thanks for your help!

Skip

Skip

That will depend on whether your "nulls" mean no measurement was made, in
which case your average would include fewer fields, or the field value is
"0" (like the count of coins in my pocket right now).

If you are sure you have Nulls, and don't wish them included in your
calculation, exclude them from the query that does the calculation by using
"Is Not Null" as a selection criterion. Then your counts and sums will only
use values.

If you want to include all potential measurements, including Nulls, use the
Nz() function (NullToZero) to convert Nulls to a value that Count and Sum
can use, say "0"!

Good luck!

Jeff Boyce
<Access MVP>