Solved

Access Update query syntax

Posted on 2014-04-02
5
365 Views
Last Modified: 2014-04-03
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
Comment
Question by:UserName935
  • 2
  • 2
5 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39973062
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
 

Author Closing Comment

by:UserName935
ID: 39973363
Thank you very much !!!!
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39973409
UserName935,

Thank you for accepting my answer. For future reference you may want to look at the grading standards for EE.
0
 

Author Comment

by:UserName935
ID: 39974958
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question