Avg in a query

[Radhika]

I have a query where I am trying to calculate the average of two fields. The
two fields of importance are called 'OPSE 10ml' and OPSE 10ml 2', each of
which come from another single query. I am trying to make the query look like
this (in datasheet veiw):

ID # Date Phase OPSE 10ml OPSE 10ml 2 OPSE
10ml Avg
1111 01/01/2001 1 10 10
10
1111 02/01/2001 2 4 6
5

So for each ID# there should be an average OPSE 10ml score. This is the code
i used:
OPSE 10 ml Avg: (Nz([OPSE 10ml])+Nz([OPSE 10ml 2]))/2

This works well when both values of OPSE 10ml and OPSE 10ml 2 are present.
However, if one value (for example, OPSE 10ml) is missing, the expression
causes it to be counted as 0. Therefore, if in phase 2, '4' is missing, the
average will be '3'. However, I would like the average to show up as '6'. How
do I change my expression to exclude a value if it is null, instead of
calculating it as '0'.

Thankyou,
Radhika

 

[Jeff Boyce]

If you were using a spreadsheet, you would probably arrange your data as
you've described. In Access, however, the Avg() function works on columns
of data (multiple records' data), rather than on multiple fields (in a
single record).

It is possible to calculate an average "across" fields, but you (and Access)
will have to work much harder to accomplish this.

Moreover, it appears you are trying to store the calculated average in
another column. Again, this is how you'd do it with a spreadsheet, but
Access is a relational database. If you can calculate a value from other
fields' values, you rarely need to (or should) store that calculated value.
You can use a query to (re-)calculate it whenever you need it.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Try this --
OPSE 10 ml Avg: (0+Nz([OPSE 10ml],[OPSE 10ml 2])+Nz([OPSE 10ml 2],[OPSE
10ml]))/2

For some odd reason I had to insert the '0+' to make it work correctly.

 

[Radhika]

That worked! Thankyou!

Try this --
OPSE 10 ml Avg: (0+Nz([OPSE 10ml],[OPSE 10ml 2])+Nz([OPSE 10ml 2],[OPSE
10ml]))/2

For some odd reason I had to insert the '0+' to make it work correctly.

--
KARL DEWEY
Build a little - Test a little

I have a query where I am trying to calculate the average of two fields. The
two fields of importance are called 'OPSE 10ml' and OPSE 10ml 2', each of
which come from another single query. I am trying to make the query look like
this (in datasheet veiw):

ID # Date Phase OPSE 10ml OPSE 10ml 2 OPSE
10ml Avg
1111 01/01/2001 1 10 10
10
1111 02/01/2001 2 4 6
5

So for each ID# there should be an average OPSE 10ml score. This is the code
i used:
OPSE 10 ml Avg: (Nz([OPSE 10ml])+Nz([OPSE 10ml 2]))/2

This works well when both values of OPSE 10ml and OPSE 10ml 2 are present.
However, if one value (for example, OPSE 10ml) is missing, the expression
causes it to be counted as 0. Therefore, if in phase 2, '4' is missing, the
average will be '3'. However, I would like the average to show up as '6'. How
do I change my expression to exclude a value if it is null, instead of
calculating it as '0'.

Thankyou,
Radhika