Solved

Update table 1 and get data table 2

Posted on 2012-12-27
5
303 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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modal form 11 30
Run SQL Server Proc from Access 11 31
MS Access from Delphi 31 30
Progress bar in access form 11 23
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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