Forum Discussion
Solved
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...
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
It worked! Thank you George!
Continued success with your project.