Average (but not counting null values) ???

  • Thread starter Thread starter marco_pb via AccessMonster.com
  • Start date Start date
M

marco_pb via AccessMonster.com

Hi.. I really thank you for your helps.. I thank you in advance for your
sharing of knowledge.

I have got a problem. for ex.
I got column A,B,C,D
I want to have an average vallue of A,B,C,D.. but not all column is filled.
ex: A = 1 B = 1 C = 1 D = 1 the AVG will be 1
BUT if I only had A = 1 B = 1 D = 1, I want the result still 1

How can I make the query?
please help me.. I'm running out of time T.T

thank you.. thank you.. tahnk you...
 
If you are trying to add the fields across a record, you have a bad data
design.

You need to redesign the database so you have multiple *records* for these
values in a related table, instead of repeating fields all in one record.
Once you have done that, you can use Avg() in a Totals query and it will
ignore any nulls and give you the correct average. (Chances are you won't
even need the null records anyway.)

If you want to persist with your spreadsheet-like design instead of a
correct database design, you will need to type a very convoluted expression
into the Field row in a query. It will be something like this:

(Nz([A],0) + Nz(, 0) + ...) / (IIf([A] Is Null, 0, 1) + IIf( Is
Null, 0, 1) + ...)
 
Back
Top