I have a query that seems to work only sometimes, and cannot seem to pin down
why:

UPDATE AVGBAL SET [SIX MO AVG] = IIf(ISNULL([AVGBAL.mar09]) And
ISNULL([AVGBAL.feb09]) And ISNULL([AVGBAL.jan09]) And ISNULL([AVGBAL.dec08])
And ISNULL([AVGBAL.nov08]) And
ISNULL([AVGBAL.oct08]),yy0,(NZ([AVGBAL.mar09])+NZ([AVGBAL.feb09])+NZ([AVGBAL.jan09])+NZ([AVGBAL.dec08])+NZ([AVGBAL.nov08])+([AVGBAL.oct08]))/(IIf(ISNULL([AVGBAL.mar09]),0,1)+IIf(ISNULL([AVGBAL.feb09]),0,1)+IIf(ISNULL([AVGBAL.jan09]),0,1)+IIf(ISNULL([AVGBAL.dec08]),0,1)+IIf(ISNULL([AVGBAL.nov08]),0,1)+IIf(ISNULL([AVGBAL.oct08]),0,1)));

A couple of things -- What is 'yy0'?
You can not average averages as you are trying to do
Mathematically you can not do it.
Average 50, 50, & 50 = 50. Average 10, 100, 50, 50, and 75 = 57

Average 50 & 57 = 53.5
Average 50, 50, & 50, 10, 100, 50, 50, and 75 = 54.375

You must use the root data to find the correct average.

You have your table laid out like a spreadsheet -- that is a no, no.
Use a union query to put your data in a 'normalized' table.

And finally, DO NOT STORE calculated information as the data it is derived
from may change and therefore the calculation will be wrong. Always compute
it when needed to ensure current data,

"new kid" wrote:

> I have a query that seems to work only sometimes, and cannot seem to pin down
> why:
>
> UPDATE AVGBAL SET [SIX MO AVG] = IIf(ISNULL([AVGBAL.mar09]) And
> ISNULL([AVGBAL.feb09]) And ISNULL([AVGBAL.jan09]) And ISNULL([AVGBAL.dec08])
> And ISNULL([AVGBAL.nov08]) And
> ISNULL([AVGBAL.oct08]),yy0,(NZ([AVGBAL.mar09])+NZ([AVGBAL.feb09])+NZ([AVGBAL.jan09])+NZ([AVGBAL.dec08])+NZ([AVGBAL.nov08])+([AVGBAL.oct08]))/(IIf(ISNULL([AVGBAL.mar09]),0,1)+IIf(ISNULL([AVGBAL.feb09]),0,1)+IIf(ISNULL([AVGBAL.jan09]),0,1)+IIf(ISNULL([AVGBAL.dec08]),0,1)+IIf(ISNULL([AVGBAL.nov08]),0,1)+IIf(ISNULL([AVGBAL.oct08]),0,1)));