Trying to create Access report that subtracts payments from what is owed

Greetings, I've come across something that has me baffled on how to correct. What I want is a student balance only appearing once and the payments made to subtract that total balance. The issue...

access

George_Hepworth
Mar 20, 2021
Andy28348

What you need is an aggregate query that sums the payments.

Here's how I would do that, based on the information available from your screen shots:

SELECT SPT.StudentID, SPT.PaymentBalance, Payments.PaidAmount, SPT.PaymentBalance - Payments.PaidAmount AS RemainingBalance
FROM StudentProfileTable AS SPT
Left Outer JOIN
(
SELECT StudentID, Sum([Payment]) AS PaidAmount
FROM PaymentTable
GROUP BY StudentID
) AS Payments ON SPT.StudentID = Payments.StudentID

George_Hepworth

Silver Contributor

Mar 20, 2021

Andy28348

What you need is an aggregate query that sums the payments.

Here's how I would do that, based on the information available from your screen shots:

SELECT SPT.StudentID, SPT.PaymentBalance, Payments.PaidAmount, SPT.PaymentBalance - Payments.PaidAmount AS RemainingBalance
FROM StudentProfileTable AS SPT
Left Outer JOIN
(
SELECT StudentID, Sum([Payment]) AS PaidAmount
FROM PaymentTable
GROUP BY StudentID
) AS Payments ON SPT.StudentID = Payments.StudentID

Andy28348
Copper Contributor
Mar 21, 2021
It worked! Thank you George!
- George_Hepworth
  Silver Contributor
  Mar 21, 2021
  Continued success with your project.

Forum Discussion

Trying to create Access report that subtracts payments from what is owed