Averaging fields with null values

[Guest]

Is there a secret to averaging fields that contain null values? We are
working on a query that deals with daily information where there is not
information available for each day for each individual.

 

[Matthias Klaey]

Is there a secret to averaging fields that contain null values? We are
working on a query that deals with daily information where there is not
information available for each day for each individual.

Straight from the help file:

"The average calculated by Avg is the arithmetic mean (the sum of the
values divided by the number of values). You could use Avg, for
example, to calculate average freight cost.

The Avg function does not include any Null fields in the calculation."

So there is really no secret here.

HTH
Matthias Kläy

 

[Arvin Meyer [MVP]]

You need to change the Null to Zero. Use the NZ() function:

Avg(NZ([MyField],0))

 

[Guest]

What does the "0" after The [MyField] mean? The problem is that we are
trying work on some QC scores for our employees. We need for the Null not to
be counted at all.

Example: If we have an employee that worked 5 days out of seven, and they
had 5 perfect scores, putting in the 0 causes the total to be divided by by 7
instead of the 5 for the actual days worked, which changes their perfect
score to 71.428571.

Does this make sense?

You need to change the Null to Zero. Use the NZ() function:

Avg(NZ([MyField],0))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is there a secret to averaging fields that contain null values? We are
working on a query that deals with daily information where there is not
information available for each day for each individual.

 

[Grover Park George]

Exclude the records which have Null values in this field by including a
Where clause in the query.

Where Is NotNull([MyField])

--
George Hepworth
Access MVP
www.gpcdata.com

What does the "0" after The [MyField] mean? The problem is that we are
trying work on some QC scores for our employees. We need for the Null not
to
be counted at all.

Example: If we have an employee that worked 5 days out of seven, and they
had 5 perfect scores, putting in the 0 causes the total to be divided by
by 7
instead of the 5 for the actual days worked, which changes their perfect
score to 71.428571.

Does this make sense?

You need to change the Null to Zero. Use the NZ() function:

Avg(NZ([MyField],0))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is there a secret to averaging fields that contain null values? We are
working on a query that deals with daily information where there is not
information available for each day for each individual.

 

[Grover Park George]

Or more accurately, Not IsNull([YourField]). Sorry for the fumble fingers.

--
George Hepworth
Access MVP
www.gpcdata.com

Exclude the records which have Null values in this field by including a
Where clause in the query.

Where Is NotNull([MyField])

--
George Hepworth
Access MVP
www.gpcdata.com

What does the "0" after The [MyField] mean? The problem is that we are
trying work on some QC scores for our employees. We need for the Null
not to
be counted at all.

Example: If we have an employee that worked 5 days out of seven, and
they
had 5 perfect scores, putting in the 0 causes the total to be divided by
by 7
instead of the 5 for the actual days worked, which changes their perfect
score to 71.428571.

Does this make sense?

You need to change the Null to Zero. Use the NZ() function:

Avg(NZ([MyField],0))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message Is there a secret to averaging fields that contain null values? We
are
working on a query that deals with daily information where there is
not
information available for each day for each individual.

 

[Arvin Meyer [MVP]]

Pardon me. I thought your request was to have an average of all values
including those without a value to be included as 0. To just include the
rows with values, simply use a Totals query and change the Group By default
to Avg. Nulls are ignored.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

What does the "0" after The [MyField] mean? The problem is that we are
trying work on some QC scores for our employees. We need for the Null not
to
be counted at all.

Example: If we have an employee that worked 5 days out of seven, and they
had 5 perfect scores, putting in the 0 causes the total to be divided by
by 7
instead of the 5 for the actual days worked, which changes their perfect
score to 71.428571.

Does this make sense?

You need to change the Null to Zero. Use the NZ() function:

Avg(NZ([MyField],0))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is there a secret to averaging fields that contain null values? We are
working on a query that deals with daily information where there is not
information available for each day for each individual.

 

[Pat Hartman \(MVP\)]

Null values are NOT included when you use the Avg() function.
For example, the average of "3,0,9" is 4 whereas the average of "3, null, 9"
is 6
If you want your average to be 4, you must use the Nz() function to convert
null values to zeros as Arvin suggested. If you want your average to be 6,
remove the 0 that Access automatically places as the default for numeric
fields. That will change the default to null. You would also have to
update all existing data but the problem with that is, there is usually no
way to separate real 0 values from those that should have been null.

What does the "0" after The [MyField] mean? The problem is that we are
trying work on some QC scores for our employees. We need for the Null not
to
be counted at all.

Example: If we have an employee that worked 5 days out of seven, and they
had 5 perfect scores, putting in the 0 causes the total to be divided by
by 7
instead of the 5 for the actual days worked, which changes their perfect
score to 71.428571.

Does this make sense?

You need to change the Null to Zero. Use the NZ() function:

Avg(NZ([MyField],0))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is there a secret to averaging fields that contain null values? We are
working on a query that deals with daily information where there is not
information available for each day for each individual.