Forum Discussion
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 I have happening is due that they are related it keeps adding the Balance with the subtotals making the final balance incorrect. For example with "Bob"; the Balance is repeated 3 times and added to the total balance and only subtracting the 1,500 (2,500 * 3 - 1,500 = 6,000). I need to find some way that the 2,500 is only mentioned once and letting the payments subtract from the balance. I'm assuming that there's a way to restructure the data to make this somehow work either changing the table information or something. I'm really stuck on making this happen. In addition, I attached images of the issue. If anyone has some suggestions feel free to let me know, thank you in advanced.
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
3 Replies
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.
