SOLVED

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

Copper Contributor

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.  

3 Replies
best response confirmed by Andy28348 (Copper Contributor)
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

It worked! Thank you George!
Continued success with your project.
1 best response

Accepted Solutions
best response confirmed by Andy28348 (Copper Contributor)
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

View solution in original post