SUMIFS Calculated field from 1 Access Table to Another

S

SAM256

Registered User.
Local time
Today, 11:37
Joined
Jul 18, 2019
Messages
32
Hi All,

I need some help from the experts.

I need to create a calculated field (named "Total_Payment" - highlighted yellow in the attached excel sheet) in 1 table called "Loan_Schedules" that sums values from another table called "Payments" based on 2 criteria (the Loan_ID and Schedule_ID) and updates the field column (Total_Payment)

I have attached an excel file illustrating what i am trying to achieve.

Please kindly assist.
 

Attachments

Build queries:

Query1: PaymentTotals
SELECT Payments.Loan_ID, Payments.Schedule_ID, Sum(Payments.Amount_Paid) AS SumOfAmount_Paid
FROM Payments
GROUP BY Payments.Loan_ID, Payments.Schedule_ID;

Query2:
SELECT Loan_Schedules.*, PaymentTotals.SumOfAmount_Paid
FROM PaymentTotals RIGHT JOIN Loan_Schedules ON (PaymentTotals.Schedule_ID = Loan_Schedules.Schedule_ID) AND (PaymentTotals.Loan_ID = Loan_Schedules.Loan_ID);
 
Hi June7,

Thanks for the advise.

However i get the following error msg "syntax error in join operation"

Attached is the sample DB.

Kindly assist rectify this.
 

Attachments

I imported the Excel sheets into Access. The queries I posted worked perfectly. I copy/pasted my queries into your db and they worked.

You did not provide your attempted query SQL so don't know why it errors.
 
You must log in or register to reply here.

Similar threads

S
Access table protection from connections and DBreplications
Replies
13
Views
246
isladogs
isladogs
I
NEED HELP! Adding a button on the form to move a record from a table to another
Replies
5
Views
151
The_Doc_Man
The_Doc_Man
Rodeo Clown
Access import function converts text field to scientific notation
Replies
9
Views
482
cheekybuddha
cheekybuddha
A
Copy field 1 to field 2 problem
Replies
5
Views
499
Pat Hartman
P
R
Solved Importing and calculating the data
Replies
12
Views
978
Robrobby
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom