SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2225401%22%20slang%3D%22en-US%22%3ETrying%20to%20create%20Access%20report%20that%20subtracts%20payments%20from%20what%20is%20owed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225401%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20come%20across%20something%20that%20has%20me%20baffled%20on%20how%20to%20correct.%26nbsp%3B%20What%20I%20want%20is%20a%20student%20balance%20only%20appearing%20once%20and%20the%20payments%20made%20to%20subtract%20that%20total%20balance.%26nbsp%3B%20The%20issue%20I%20have%20happening%20is%20due%20that%20they%20are%20related%20it%20keeps%20adding%20the%20Balance%20with%20the%20subtotals%20making%20the%20final%20balance%20incorrect.%26nbsp%3B%20For%20example%20with%20%22Bob%22%3B%20the%20Balance%20is%20repeated%203%20times%20and%20added%20to%20the%20total%20balance%20and%20only%20subtracting%20the%201%2C500%20(2%2C500%20*%203%20-%201%2C500%20%3D%206%2C000).%26nbsp%3B%20I%20need%20to%20find%20some%20way%20that%20the%202%2C500%20is%20only%20mentioned%20once%20and%20letting%20the%20payments%20subtract%20from%20the%20balance.%26nbsp%3B%20I'm%20assuming%20that%20there's%20a%20way%20to%20restructure%20the%20data%20to%20make%20this%20somehow%20work%20either%20changing%20the%20table%20information%20or%20something.%26nbsp%3B%20I'm%20really%20stuck%20on%20making%20this%20happen.%26nbsp%3B%20In%20addition%2C%20I%20attached%20images%20of%20the%20issue.%20If%20anyone%20has%20some%20suggestions%20feel%20free%20to%20let%20me%20know%2C%20thank%20you%20in%20advanced.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2225401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2225477%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20create%20Access%20report%20that%20subtracts%20payments%20from%20what%20is%20owed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1003686%22%20target%3D%22_blank%22%3E%40Andy28348%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you%20need%20is%20an%20aggregate%20query%20that%20sums%20the%20payments.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20how%20I%20would%20do%20that%2C%20based%20on%20the%20information%20available%20from%20your%20screen%20shots%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20SPT.StudentID%2C%20SPT.PaymentBalance%2C%20Payments.PaidAmount%2C%20SPT.PaymentBalance%20-%20Payments.PaidAmount%20AS%20RemainingBalance%3CBR%20%2F%3EFROM%20StudentProfileTable%20AS%20SPT%3CBR%20%2F%3ELeft%20Outer%20JOIN%3CBR%20%2F%3E(%3CBR%20%2F%3ESELECT%20StudentID%2C%20Sum(%5BPayment%5D)%20AS%20PaidAmount%3CBR%20%2F%3EFROM%20PaymentTable%3CBR%20%2F%3EGROUP%20BY%20StudentID%3CBR%20%2F%3E)%20AS%20Payments%20ON%20SPT.StudentID%20%3D%20Payments.StudentID%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.