Updating row's values based on sum of values in other rows

Hi Experts,

I have a budgeting application in access.  I have a form and related table which has rows for each GL account with fields for the months in the year.

I would like to update the row (ie GL account) for Fringe benefits based on the total of amounts in the salary rows (defined by a range of values in the GL account field).

So I'd like to sum the amounts in each month for salaries, multiply by the fringe rate, and put that total in the Fringe line. (I realize that this violates the 'no totals in tables' normalization rule.) This updating process will run during the form's close event; so I need to run it in VBA.

I've attempted a DSUM, but the quotes didn't work...  not sure how to specify that a single quote in the sql statement is a double quote in the dsum?

My sql statement looks like:

UPDATE tmpProjectBudgetDetail SET tmpProjectBudgetDetail.Month1 = DSum("Month1","tmpProjectBudgetDetail","Project = 'DCDNAV701' AND BudgetVersion = 'V1' AND GL_acct>='51000' and GL_Acct <= '51100'")*.28,  ...(other months omitted) ... , tmpProjectBudgetDetail.Month12 = DSum("Month12","tmpProjectBudgetDetail","Project = 'DCDNAV701' AND BudgetVersion = 'V1' AND GL_acct>='51000' and GL_Acct <= '51100'")*.28, tmpProjectBudgetDetail.FullYear = DSum("FullYear","tmpProjectBudgetDetail","Project = 'DCDNAV701' AND BudgetVersion = 'V1' AND GL_acct>='51000' and GL_Acct <= '51100'")*.28, tmpProjectBudgetDetail.Subdetail = True
WHERE (((tmpProjectBudgetDetail.Project)="DCDNAV701") AND ((tmpProjectBudgetDetail.BudgetVersion)="V1") AND ((tmpProjectBudgetDetail.GL_acct)="51260"));

Any thoughts?
Start Free Trial