Averaging Fields

  • Thread starter Thread starter ScottRA
  • Start date Start date
S

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
 
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

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
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?
Click to expand...

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.
 
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
Click to expand...

<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.
 
Back
Top