I am trying to do an update query and having problems. It seems pretty simple but I'm obviously missing something. I have two fields in the query, one is the year and the other is a number. I need the query to update the year to 2002 where year = 2001 and sum(number) <>0. However, it won't let me do sum the number.
Here is the SQL for the statement:
UPDATE FixedFields INNER JOIN VarFields ON FixedFields.InitiativeNo = VarFields.InitiativeNo SET VarFields.PlanYear = 2002
WHERE (((VarFields.PlanYear)=2001) AND (sum(VarFields.[4DWFTE])<>0));
It then returns error "cannot have aggrigate function in where clause"
I am not a programmer but know basic sql so if you could keep an answer simple that would be great. Thanks for your help!
Here is the SQL for the statement:
UPDATE FixedFields INNER JOIN VarFields ON FixedFields.InitiativeNo = VarFields.InitiativeNo SET VarFields.PlanYear = 2002
WHERE (((VarFields.PlanYear)=2001) AND (sum(VarFields.[4DWFTE])<>0));
It then returns error "cannot have aggrigate function in where clause"
I am not a programmer but know basic sql so if you could keep an answer simple that would be great. Thanks for your help!