Averaging Fields

[ScottRA]

Hi,

I am developing in MS Access 2003 and I'm trying to have a form
automatically calculate average values for me. I know I should calculate
these values on the fly when the data is queried. I ultimately would like a
button which the user would click to calcuate the values.

The form is something like this


Field 1 Field 2 Field 3 Field 4 Average of Field 1-4
145 145 138 142.67



Any field may have a null value as well (like field 4), so that is why i
haven't done something like me.average = me.field1+ etc / 4



Any suggestions?



Many thanks for any help

Cheers

Scott

 

[Linq Adams via AccessMonster.com]

Use the Nz() function to change your Null(s) to another value, in this case
to Zeros:

Me.Average = (Nz(Field1,0) + Nz(Field2,0) + Nz(Field3, 0) + Nz(Field4, 0))/4

 

[John W. Vinson]

Hi,

I am developing in MS Access 2003 and I'm trying to have a form
automatically calculate average values for me. I know I should calculate
these values on the fly when the data is queried. I ultimately would like a
button which the user would click to calcuate the values.

The form is something like this


Field 1 Field 2 Field 3 Field 4 Average of Field 1-4
145 145 138 142.67



Any field may have a null value as well (like field 4), so that is why i
haven't done something like me.average = me.field1+ etc / 4



Any suggestions?

Well... I'd suggest that you not try to use Access as if it were a
spreadsheet; it's not!

"Fields are expensive, records are cheap". If you have zero to four values
related to some entity, you should have two tables in a one to many
relationship; rather than four FIELDS for the values you would have four
RECORDS, which would let you use a Totals query to average them.

With your current "spreadsheet" design you'll need to use a snarky expression
like

(NZ(Field1]) + NZ([Field2]) + NZ([Field3]) + NZ([Field4)) / (4 +
IsNull([Field1]) + IsNull([Field2]) + IsNull([Field3]) + IsNull([Field4]))

with a criterion to be sure you have at least one non-null value.

 

[John W. Vinson]

Use the Nz() function to change your Null(s) to another value, in this case
to Zeros:

Me.Average = (Nz(Field1,0) + Nz(Field2,0) + Nz(Field3, 0) + Nz(Field4, 0))/4

<g> sorry Linq - he tried that. That would give the average of (1, NULL, NULL,
NULL) as 0.25.

Restructuring tables would be the best bet IMO.