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

[Andy28348]

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.  

[#2 · Accepted solution]

@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

[#3]

It worked! Thank you George!

[#4]

Continued success with your project.