[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Update table 1 and get data table 2

Posted on 2012-12-27
5
Medium Priority
?
320 Views
Last Modified: 2012-12-28
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
0
Comment
Question by:Mehram
5 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38723007
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
0
 

Author Comment

by:Mehram
ID: 38723030
Where I need to past it, can you edit please in my mdf
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 38723064
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);
0
 

Author Comment

by:Mehram
ID: 38723077
Sir, Where I need to past it to work, can you edit please in my mdf
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1500 total points
ID: 38723104
Just based on your posted query, it looks like what you should have is...

Update Emp_Loan  Set LDeducted=DSum("[DAmount]", "[Emp_Loan_Deducted]", "EmpID=" & Emp_Loan.EMPID)

This assumes EMPID is a number field.

The query has no specific reference to any form or report but you can run it anywhere you like.  

You cannot create an update query in Access that has a Group By expression  or any aggregate function Sum/Count/Avg etc; so the previous responses will not work.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

590 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