Calculating an Average over multi fields

  • Thread starter Thread starter Ian Tranter
  • Start date Start date
I

Ian Tranter

I have a record that has multiple fields containing the
weights of items, that is
A "worksOrder" has a batch quantity of 100 parts & a % of
them need to be weighed. The user inputs the weights into
fields A1 to A4, depending on the batch size 1 or two
fields will be filled in.
I have created a query that lists the works Order's and
shows the 4 fields that need to be averaged.
The fields shown are WorksOrder, A1 to A4
My question is
How do I enter an expression to calculate the average of
all the fields A1 to A4?
I have input:-
AVG([A1]+[A2]+[A3]+[A4]), the answer comes out at the sum
of all the fields not the average, or blank if 1 field is
not input?
Help please??????
 
John,
Thanks for the formula, it works a treat, BTW, I'm new to
Access as our company has just changed from Lotus to
Microsoft, Guess who's responsible for translating the
commonly used databases to access.
Yes, I think you are definitely correct when you said
that the structure is wrong, I will probably re-think the
entire database.
-----Original Message-----
To take care of the blank fields you would need to use the NZ function.

To get the sum of these four fields you would need

Nz([A1])+Nz([A2])+Nz([A3])+Nz([A4])

To get the average of that you need to count the number of fields with a value
4 + IsNull([A1]) + IsNull([a2]) + IsNull([a3]) + IsNull([a4])

To get the average you will need to divide the first formula by the second and
account for zero in the second formula. Let's call the formulas A and B

IIF(B<>0,A/B,0)

So putting that all together

IIF(4 + IsNull([A1]) + IsNull([a2]) + IsNull([a3]) +
Click to expand...
IsNull([a4]) said:
(Nz([A1])+Nz([A2])+Nz([A3])+Nz([A4]))/
(4 + IsNull([A1]) + IsNull([a2]) + IsNull([a3]) + IsNull([a4])),
0)

Avg works for one field over multiple records. That should be a clue that your
structure is incorrect and you have a record for each input of weights that is
tied to the worksorder or batch parts.


Ian said:
I have a record that has multiple fields containing the
weights of items, that is
A "worksOrder" has a batch quantity of 100 parts & a % of
them need to be weighed. The user inputs the weights into
fields A1 to A4, depending on the batch size 1 or two
fields will be filled in.
I have created a query that lists the works Order's and
shows the 4 fields that need to be averaged.
The fields shown are WorksOrder, A1 to A4
My question is
How do I enter an expression to calculate the average of
all the fields A1 to A4?
I have input:-
AVG([A1]+[A2]+[A3]+[A4]), the answer comes out at the sum
of all the fields not the average, or blank if 1 field is
not input?
Help please??????
Click to expand...
.
Click to expand...
 
Back
Top