Need help ASAP-update query with sum 1

[jlbartley]

I posted this earlier and never got an answer so I thought I would try again.

I've got one table called VarFields that has fields called InitiativeNo, PlanYear, and 4DWFTE. I want to update all of the PlanYear from 2001 to 2002 where the sum(4DWFTE) <> 0. When I try this it says that I cannot have an aggrigate function in a where clause.

I tried to do an inner select using this statement:
UPDATE VarFields SET VarFields.PlanYear = 2002
WHERE (((VarFields.InitiativeNo) In (select [VarFields].[InitiativeNo] FROM VarFields WHERE sum([VarFields].[4DWFTE]<>0)
GROUP BY [VarFields].[InitiativeNo])));
but get the same error. Does anyone have an idea of how to do this.

Some of the data in the table looks like:
InitiativeNo PlanYear 4DWFTE
8 2001 1.00
9 2001 0.25
9 2001 0.75
10 2001 -1.00
10 2001 1.00
12 2001 1.00
13 2001 1.00
15 2001 -1.00
15 2001 1.00

Please let me know any suggestions you may have. Thank you.

 

[geopat]

You should use HAVING instead of WHERE, if you want to use aggregrate values as a condition. Also, HAVING has to be put after GROUP BY.

UPDATE VarFields SET VarFields.PlanYear = 2002
WHERE (((VarFields.InitiativeNo) In (select [VarFields].[InitiativeNo] FROM VarFields
GROUP BY [VarFields].[InitiativeNo]
HAVING sum([VarFields].[4DWFTE]) <> 0)
)));

 

[jlbartley]

Thank you so much! This worked out great. It was exactly what I was looking for!