averaging data

[new kid]

I have a query that seems to work only sometmes, and cannot figure out 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]),0,(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)));

 

[KC-Mass]

You need to fix your data structure. Do do not want to set up fields for
literal months eg Jan08; you don't want to store the result of a calculation
( eg average).
BUT
assuming you are up against it with what you have try somethin like:
Avg(NZ(Mar09), NZ(Feb09), etc)

Look at Avg and Davg in the help file or google groups for either.

Regards

Kevin

 

[Piet Linden]

I have a query that seems to work only sometmes, and cannot figure out 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]),0,(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)));

The other thing you shouldn't do is store facts (dates) in field
names. Makes fixing your data a PITA. I know, because I have had to
do it.

Your structure should be
....
BalanceDate DATE,
AvgBalance currency,
....

And then you can just do a sum...
SELECT SUM(AvgBal)
FROM BalanceTable
WHERE BalanceDate BETWEEN #1/1/09# AND #4/1/09#...