Hi Experts,
I am trying to develop a Emp App for a small firm. I am using two table for Loan, detail as follows:
Table 1 Emp_Loan in which loan availed
Table 2 Emp_Loan_Deduction in which monthly loan deduction logged
I need to update table 1 when it is access by user either through report or form. like this, please help to do so.
Update Emp_Loan (Emp_Loan.LDeducted=Sum(Emp_Loan_Deducted .DAmount)
FROM Emp_Loan_Deducted Join Emp_Loan on Emp_Loan_Deducted.EmpID=Emp_Loan.EMPID
Group by Emp_Loan_Deducted.EmpID HDP.accdb
Microsoft SQL Server 2005Microsoft Access
Last Comment
peter57r
8/22/2022 - Mon
appari
try this
Update A
Set LDeducted=DAmount_Sum
FROM Emp_Loan A Join
( Select EmpID, Sum(DAmount) DAmount_Sum From Emp_Loan_Deducted
Group by Emp_Loan_Deducted.EmpID) B
on A.EmpID=B.EMPID
Mehram
ASKER
Where I need to past it, can you edit please in my mdf
k_murli_krishna
UPDATE Emp_Loan el
SET el.LDeducted = (SELECT SUM(eld.DAmount)
FROM Emp_Loan_Deducted eld
WHERE eld.EmpID=el.EMPID
GROUP BY eld.EmpID);
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mehram
ASKER
Sir, Where I need to past it to work, can you edit please in my mdf
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Update A
Set LDeducted=DAmount_Sum
FROM Emp_Loan A Join
( Select EmpID, Sum(DAmount) DAmount_Sum From Emp_Loan_Deducted
Group by Emp_Loan_Deducted.EmpID) B
on A.EmpID=B.EMPID