• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Access Update query syntax

Good Afternoon Guru's,

I am having an issue with an update query in ms access.

table structure:
                                         month1  month2   month3   month4  etc.......
grp1   money                        5.00       6.23        52.3         54.21
grp1   something
grp1   something1
grp1   average
grp2   money                        5.36        8.21        4.14          5.98
grp2   something
grp2   something1
grp2   average

Issue: We need to update each grp's 'average' line for each month based on the sum of all values in each respective grps 'money' line.
This is what is created so far, and I am running into syntax issues:

UPDATE ChartDBSource_Template
SET ChartDBSource_Template.Mar = ([ChartDBSource_Budget].[Mar] + [ChartDBSource_Budget].[Jan] + ([ChartDBSource_Budget].[Feb] + ([ChartDBSource_Budget].[Mar] + ([ChartDBSource_Budget].[Apr] + ([ChartDBSource_Budget].[May] + ([ChartDBSource_Budget].[Jun] + ([ChartDBSource_Budget].[Jul] + ([ChartDBSource_Budget].[Aug] + ([ChartDBSource_Budget].[Sep] + ([ChartDBSource_Budget].[Oct] + ([ChartDBSource_Budget].[Nov] + ([ChartDBSource_Budget].[Dec] / 12)
WHERE ChartDBSource_Template.CHART_BIN like 'BudAvg';
0
UserName935
Asked:
UserName935
  • 2
  • 2
1 Solution
 
Jim P.Commented:
Your parentheses were off. The Nz is a IfNull function. Try this:

UPDATE ChartDBSource_Template
SET ChartDBSource_Template.Mar = ((Nz([ChartDBSource_Budget].[Mar],0) +
                                  Nz([ChartDBSource_Budget].[Jan],0) +
                                  Nz([ChartDBSource_Budget].[Feb],0) +
                                  Nz([ChartDBSource_Budget].[Mar],0) +
                                  Nz([ChartDBSource_Budget].[Apr],0) +
                                  Nz([ChartDBSource_Budget].[May],0) +
                                  Nz([ChartDBSource_Budget].[Jun],0) +
                                  Nz([ChartDBSource_Budget].[Jul],0) +
                                  Nz([ChartDBSource_Budget].[Aug],0) +
                                  Nz([ChartDBSource_Budget].[Sep],0) +
                                  Nz([ChartDBSource_Budget].[Oct],0) +
                                  Nz([ChartDBSource_Budget].[Nov],0) +
                                  Nz([ChartDBSource_Budget].[Dec],0) )/ 12)
WHERE ChartDBSource_Template.CHART_BIN like 'BudAvg';

Open in new window

0
 
UserName935Author Commented:
Thank you very much !!!!
0
 
Jim P.Commented:
UserName935,

Thank you for accepting my answer. For future reference you may want to look at the grading standards for EE.
0
 
UserName935Author Commented:
I apologize if I failed on the grading task.  

If it is possible, feel free to change it to what it is you desire, and, thank you again for the assistance.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now