SUMIFS Calculated field from 1 Access Table to Another (1 Viewer)

S

SAM256

Registered User.

Local time

Today, 14:01

Joined

Jul 18, 2019

Messages

32

• Aug 2, 2019

• #1

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

• Tables.xlsx

  10.4 KB · Views: 91

June7

AWF VIP

Local time

Today, 13:01

Joined

Mar 9, 2014

Messages

5,466

• Aug 2, 2019

• #2

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);

 

S

SAM256

Registered User.

Local time

Today, 14:01

Joined

Jul 18, 2019

Messages

32

• Aug 2, 2019

• #3

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

• Sample_DB.zip

  533.8 KB · Views: 113

Gasman

Enthusiastic Amateur

Local time

Today, 22:01

Joined

Sep 21, 2011

Messages

14,238

• Aug 2, 2019

• #4

June7,

Just so you are aware, the o/p has also asked this in https://www.access-programmers.co.uk/forums/showthread.php?t=305946 where arnelgp is assisting.

 

June7

AWF VIP

Local time

Today, 13:01

Joined

Mar 9, 2014

Messages

5,466

• Aug 2, 2019

• #5

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.

 

S

SAM256

Registered User.

Local time

Today, 14:01

Joined

Jul 18, 2019

Messages

32

• Aug 2, 2019

• #6

Thanks June7.

However arnelgp was able to assist with the exact SQL script required on the following forum.
https://www.access-programmers.co.uk...d.php?t=305946

Thank you once again for your assistance.