## Re: Another complicated query but perhaps possible

Originally Posted by Zvoni
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.

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