Quote Originally Posted by Zvoni View Post
not possible.
how do you want to get the fully paid teachers, when you want the SUM across teachers?
same with students.

this will only work, if you ignore teachers/students to receive a total sum
I think there is slight thing to modify in this code.
When I print RS!SumPayment, I get correct result, except if a subject is duplicated.
In this scenario:
Steven Spielberg's remaining = 0 (for Math)
James Cameron's remaining = 0 (for Physics)

Steven Spielberg's SumPayment = 300 (for Math)
James Cameron's SumPayment = 50 (for Physics)

this code gives correct output: 350

but if we add for example:
Stanley Kubrick's remaining = 0 (for Physics)
Stanley Kubrick's's SumPayment = 100 (for Physics)

The code give 350 though it should give 450.
Physics is duplicated. The code doesn't take that into account.

However if we add:
Stanley Kubrick's remaining = 0 (for English)
Stanley Kubrick's's SumPayment = 100 (for English)
The code gives correct result: 450

Code:
WITH
    TR  AS (SELECT SC.ID, SC.Student_ID, COALESCE(SC.Price, S.SubjectPrice) AS SubjectPrice, 
            S.SName As SubjectName, TE.TName As TeacherName, SC.NumOfSessions, 
            COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions As TotalSubjectPrice,
            SUM(COALESCE(T.Payment,0)) As SumPayment, Max(T.PayDate) As LastPayDate
            FROM tbl_student_class AS SC 
            INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID
            INNER JOIN tbl_teacher AS TE ON TE.ID=TS.Teacher_ID
            INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
            LEFT JOIN tbl_transaction As T ON T.Student_Class_ID=SC.ID 
            GROUP BY SC.ID,SC.Student_ID, COALESCE(SC.Price, S.SubjectPrice), S.SName, TE.TName, SC.NumOfSessions,COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions),
    SP    AS (SELECT TR.Student_ID, SUM(TR.TotalSubjectPrice) As SumTotal, SUM(TR.SumPayment) As SumPayment 
            FROM TR GROUP BY TR.Student_ID)
            
SELECT S.STName As Student, 
COALESCE(SP.SumTotal, 0) AS SumTotal, COALESCE(SP.SumPayment, 0) AS SumPayment, 
COALESCE(SP.SumTotal, 0)-COALESCE(SP.SumPayment, 0) As Remaining

FROM tbl_student AS S 
LEFT JOIN SP ON SP.Student_ID=S.ID 
WHERE 
COALESCE(SP.SumTotal, 0)-COALESCE(SP.SumPayment, 0)=0 /*Returns Students who have paid in full */
ORDER BY S.STName
Debug.Print RS!SumPayment