Sum in an Update Query

[jlbartley]

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 &quot;cannot have aggrigate function in where clause&quot;

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!

 

[Brewman76]

The error message (in programmer-ese) is basically telling you that you cannot have a SUM function inside the WHERE part of your SQL statement.

I would post a fix (being to first create a subquery which does the SUM work then use that in your UPDATE query) but I do not quite understand what your UPDATE statement is doing. You are joining the FixedFields table with the VarFields table but the join does not seem to be serving any purpose. Explain in further detail in plain words what you want this SQL to do and then I can be of more help.

Adam

 

[jlbartley]

I have one table that I am trying to update information in. You're right that I had the FixedFields table linked and didn't need to. I have one table called VarFields that contains fields named InitiativeNo, PlanYear, 4DWFTE, etc. I am trying to update all of the PlanYears to 2002 where the plan year is currently 2001 and the sum(4DWFTE) per InitiativeNo is <>0.