Update MS Access database table using update and Aggregrate sum() function

Question

I have Two tables in my access database table1(Employee Name,Emp Number,Emp Salary) table2(Employee Name,Emp Number,Total Salary) these tables are related together using "Employee Name" and "Emp Number",How can I update "Total Salary" from table2 with the value Sum(Emp Salary) from first table"

Sergey S. · Accepted Answer · 2018-03-31 07:13:24Z

1

Query, which contains aggregated functions or uses queries with aggregated functions is not updateable. So, you can update the data in existing table using:

Temporary table. Save aggregated results in temporary table and then update working table using data from this temporary table.
If aggregating is simple and aggregate query functions can be replaced by domain aggregate functions like DSum or CDount, resulting query will be updateable and you can avoid using temporary tables

Query with domain function may look like this:

UPDATE Result
SET Result.[Total Salary] = DSum("Emp Salary", "Emp", "Employee Name='" & Replace(Result.EmpName, "'", "''") & _
    "' and [Emp Number]=" & Result.[Emp Number])
WHERE Result.[EmpName] = 'Mohan'
    AND Result.[Emp Number] = 1;

edited Mar 31, 2018 at 7:13

answered Mar 31, 2018 at 6:48

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mohan Over a year ago

Thanks for your reply Sergey...Could you please review the below query which iam using DSum.. UPDATE Result SET Result.[Total Salary] = DSum("Emp Salary", "Emp", "Employee Name='Mohan'" & Result.EmpName & "'") WHERE Result.[EmpName]='Mohan' And Result.[Emp Number]=1; when iam using above query it updating as 0 at Set field..Please review it once and let me know the updated query..

Sergey S. Over a year ago

You had an error in DSum criteria argument. Also I added duplicationg single quotes if name contains single quote.

Mohan Over a year ago

Thank you for query...but when iam using same query its showing 1 row updated after clicking YES again iam getting another pop message as "Microsoft Access can't update all the records in the update Query" Microsoft Access didn't update 1 field(s) due to type conversion failure, 0 records due to key violations, 0 records due to lock violations, 0 records due to validation rule violations" Please check once...Thanks in advacne

Mohan Over a year ago

Thanks a lot Sergey...Now it working as expected....once again thank you so much :) :)

Sergey S. Over a year ago

If the answer helped you to solve the problem, please mark it as accepted, it will help other users

Collectives™ on Stack Overflow

Update MS Access database table using update and Aggregrate sum() function

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related