0

I am trying to update a table filed taking value from another table by summing a field based on EmpID.

In tblEmpInfo EmpID is primary key. I have saw few post on this site but couldn't adopt to my file. Here is my code till yet.

UPDATE tblEmpInfo AS c 
INNER JOIN (SELECT EmpID, SUM(ProfidentFund) AS total FROM tblTransactions GROUP BY EmpID)  AS x ON c.EmpID = x.EmpID 
SET c.ProfidentFund = x.total;

Above code give me following warning and do not update value to tblEmpInfo

enter image description here

tblEmpInfo screenshot.

enter image description here

tblTransactions screenshot.

enter image description here

And my expected output.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Did you try this ? https://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access – ismetguzelgun Jul 16 '20 at 07:24
  • @ismetguzelgun I have tried `DISTINCTROW ` but no luck for me. Another thing is I am summing value of `ProfidentFund` field. – Harun24hr Jul 16 '20 at 07:39
  • 1
    Your script seems legit. I do not understand why are you getting this error really. Summing is nothing unusual it should be okay. – ismetguzelgun Jul 16 '20 at 07:44
  • 1
    [This post answer](https://stackoverflow.com/questions/62988053/access-update-with-subquery) solve my proble. – Harun24hr Jul 20 '20 at 08:05

1 Answers1

0

You can try the below -

UPDATE  c 
SET c.ProfidentFund = x.total
from tblEmpInfo AS c
INNER JOIN 
(SELECT EmpID, SUM(ProfidentFund) AS total FROM tblTransactions GROUP BY EmpID)  AS x 
    ON c.EmpID = x.EmpID
Fahmi
  • 37,315
  • 5
  • 22
  • 31