Another complicated query but perhaps possible

[Zvoni]

Wait a second: are you doing this on an InMemory-Database?
IIRC, there are some exceptions for InMemory databases

[newbie2]

Sorry I didn't notice that we have moved to page 3

are you doing this on an InMemory-Database?

No they are true tables
I can even check in some sqlite tools and I could see that:
FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE RESTRICT

[newbie2]

Zvoni
Sorry to disturb you again
This code is showing only one record.
I have three teachers who have completed their payments but only the first teacher is retrieved.
When I examine the code, everything looks perfect but the outcome is not ideal.

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage),
    CT      AS (SELECT S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
                SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
                SC.TotalSessions, COALESCE(TR.Payment,0) AS Payment,
                SC.TotalPrice*TS.TeacherPercentage/100-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.Class_ID ORDER BY TR.PayDate) As TotalTeacherPriceRemaining,
                TR.Paydate 
                FROM tbl_subject As S 
                INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
                INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
                LEFT JOIN SC ON SC.Class_ID=TS.ID
                LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID)
                
SELECT CT.SubjectName, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice, 
SUM(CT.Payment) As SumPayment, MIN(CT.TotalTeacherPriceRemaining) As TotalTeacherPriceRemaining, MAX(CT.PayDate) As PayDate 
FROM CT 
GROUP BY CT.SubjectName, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice
HAVING CT.TotalTeacherPriceRemaining=0 /* or <=0 if Payment overshoots */

When I use this code, I get the three records retrieved

StrSql = "WITH " & _
   " SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, " & _
               " TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions " & _
               " FROM tbl_student_class As SC " & _
               " INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id " & _
               " INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
               " GROUP BY SC.Class_ID, TS.TeacherPercentage) " & _
"SELECT " & _
" S.Subject As Subject, SC.TotalPrice, TName As Teacher, SC.TeacherPercentage, " & _
 " SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotal, " & _
" SC.TotalSessions, SUM(COALESCE(TR.Payment,0)) AS PaymentBySchoolToTeacher, " & _
" TR.Paydate " & _
" FROM tbl_subject As S " & _
" INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID " & _
" INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
" LEFT JOIN SC ON SC.Class_ID=TS.ID " & _
" LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID GROUP BY SC.Class_ID, SC.TeacherPercentage HAVING  TeacherTotal = PaymentBySchoolToTeacher"
Set Rs = Cnn.OpenRecordset(StrSql)

[Zvoni]

I would need the initial Values to check that (prices, payments etc.)

[newbie2]

I would need the initial Values to check that (prices, payments etc.)

Is there something I would do?

[Zvoni]

Is there something I would do?

I need the "same" INSERT's (Values) you use to check that

[newbie2]

I need the "same" INSERT's (Values) you use to check that

I created a new database and used the initial insertion codes and changed some data to make prices equal to payments and it worked properly.
I think I need to check my own database.
But what is driving me mad is that the second code is showing the three records.
Sorry sir

[Zvoni]

Ok, will use the very first sample data, and adjust accordingly.

[Zvoni]

Cannot reproduce
https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/6

DDL

CREATE TABLE "tbl_student" (
    "ID"    INTEGER,
    "STName"    TEXT NOT NULL,
    PRIMARY KEY("ID")
);

CREATE TABLE "tbl_subject" (
    "ID"    INTEGER,
    "SName"    TEXT NOT NULL,
    "SubjectPrice"    REAL NOT NULL,
    PRIMARY KEY("ID")
);

CREATE TABLE "tbl_teacher" (
    "ID"    INTEGER,
    "TName"    TEXT NOT NULL,
    PRIMARY KEY("ID")
);

CREATE TABLE "tbl_teacher_subject" (
    "ID"    INTEGER,
    "Teacher_ID"    INTEGER,
    "Subject_ID"    INTEGER,
    "TeacherPercentage"    REAL NOT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE "tbl_student_class" (
    "ID"    INTEGER,
    "Student_ID"    INTEGER,
    "Class_ID"    INTEGER,
    "Price"    REAL DEFAULT NULL,
    "NumOfSessions" INTEGER DEFAULT 1,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "tbl_transaction" (
    "ID"    INTEGER,
    "Payment"    REAL,
    "PayDate"    TEXT,
    "Student_Class_ID"    INTEGER DEFAULT NULL,
    "Teacher_Subject_ID"    INTEGER DEFAULT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY("Teacher_Subject_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT "CheckForeignKeyXOR" CHECK((Student_Class_ID IS NULL AND teacher_Subject_ID IS NOT NULL) OR (Student_Class_ID IS NOT NULL AND Teacher_Subject_ID IS NULL))
);

INSERT INTO "tbl_student" ("ID", "STName") VALUES ('1', 'Adam Sandler');
INSERT INTO "tbl_student" ("ID", "STName") VALUES ('2', 'Elijah Wood');
INSERT INTO "tbl_student" ("ID", "STName") VALUES ('3', 'Nicole Kidman');

INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('1', 'James Cameron');
INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('2', 'Steven Spielberg');
INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('3', 'Stanley Kubrick');

INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('1', 'Physics', '100.0');
INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('2', 'Math', '200.0');
INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('3', 'English', '300.0');

INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('1', '1', '1', '50.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('2', '1', '3', '25.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('3', '2', '2', '75.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('4', '3', '3', '50.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('5', '2', '1', '25.0');

INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('1', '1', '1', NULL,'1');
INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('2', '1', '3', NULL,'2');
INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('3', '2', '5', NULL,'1');
INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('4', '3', '2', NULL,'3');
INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('5', '1', '4', NULL,'1');
INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('6', '2', '2', NULL,'2');

INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('1', '10.0', '2023-01-10', '1', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('2', '20.0', '2023-02-06', '2', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('3', '30.0', '2023-02-09', '4', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('4', '10.0', '2023-02-12', '1', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('5', '15.0', '2023-02-25', '3', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('6', '10.0', '2023-02-28', '1', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('7', '60.0', '2023-01-24', '5', NULL);
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('8', '30.0', '2023-03-05', NULL, '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('9', '30.0', '2023-02-15', NULL, '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('10', '20.0', '2023-03-15', NULL, '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('11', '100.0', '2023-03-08', NULL, '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('12', '100.0', '2023-02-20', NULL, '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('13', '300.0', '2023-03-17', NULL, '3');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('14', '100.0', '2023-02-23', NULL, '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('15', '45.0', '2023-02-28', NULL, '2');

Query

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage),
    CT      AS (SELECT S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
                SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
                SC.TotalSessions, COALESCE(TR.Payment,0) AS Payment,
                SC.TotalPrice*TS.TeacherPercentage/100-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.Class_ID ORDER BY TR.PayDate) As TotalTeacherPriceRemaining,
                TR.Paydate 
                FROM tbl_subject As S 
                INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
                INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
                LEFT JOIN SC ON SC.Class_ID=TS.ID
                LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID)
                
SELECT CT.SubjectName, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice, 
SUM(CT.Payment) As SumPayment, MIN(CT.TotalTeacherPriceRemaining) As TotalTeacherPriceRemaining, MAX(CT.PayDate) As PayDate 
FROM CT 
GROUP BY CT.SubjectName, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice
HAVING CT.TotalTeacherPriceRemaining=0 /* or <=0 if Payment overshoots */

Returns

SubjectName	TotalPrice	TeacherName	TeacherPercentage	TeacherTotalPrice	SumPayment	TotalTeacherPriceRemaining	PayDate
English	1500.0	James Cameron	25.0	375.0	375.0	0.0	2023-03-08
Math	400.0	Steven Spielberg	75.0	300.0	300.0	0.0	2023-03-17
Physics	100.0	James Cameron	50.0	50.0	50.0	0.0	2023-03-15

[newbie2]

Million thanks sir

[newbie2]

Hello Zvoni
Sorry sir to bother you again
I was absent for a couple of weeks and now I'm back to my project.
I want to ask if it is possible to get rid of the field "Paid" and to retrieve the students who
have completed their payments the same way we did with teachers.
Since this code seems too advanced, I tried to adapt it on students payments but without your help it s impossible to do that.

StrSql = " WITH " & _
   " SC    AS ( SELECT   SC.Student_ID, ST.ID,SC.Class_ID, SC.NumOfSessions, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice," & _
             "   SUM(SC.NumOfSessions) As TotalSessions " & _
              "  FROM Students_Tbl  AS ST " & _
             " INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID " & _
              "  INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id " & _
               " INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
               " GROUP BY SC.Class_ID, SC.NumOfSessions), " & _
  "  CT      AS (SELECT SC.NumOfSessions, ST.STName  As Student, SC.Class_ID,S.Subject As Subject, TR.ID, TR.Teacher_Subject_ID, SC.TotalPrice, T.TName   As Teacher,  " & _
              "  SC.TotalPrice As studentTotal, " & _
               " SC.TotalSessions, COALESCE(TR.Payment,0) AS Payment, " & _
               " SC.TotalPrice-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As TotalstudentPriceRemaining, " & _
               " TR.Paydate " & _
               " FROM tbl_subject As S " & _
               " INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID " & _
               " INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
               " LEFT JOIN SC ON SC.Class_ID=TS.ID " & _
               " LEFT JOIN Students_Tbl AS ST ON SC.Student_ID=ST.ID " & _
               " LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID) " & _
" SELECT  CT.Student,  CT.Class_ID,CT.Teacher_Subject_ID,CT.ID, CT.Subject, CT.NumOfSessions, CT.TotalPrice, CT.student,  CT.studentTotal, " & _
" SUM(CT.Payment) As PaymentByStudentToSchool, MIN(CT.TotalstudentPriceRemaining) As TotalstudentPriceRemaining, MAX(CT.PayDate) As PayDate " & _
" From CT " & _
" GROUP BY CT.Subject, CT.TotalPrice, CT.student,  CT.studentTotal " & _
" HAVING CT.TotalstudentPriceRemaining=0 /* or <=0 if Payment overshoots */"

thank you

[newbie2]

I tried this code , it seems to work but there is a problem.

StrSql = " WITH " & _
   " SC    AS ( SELECT   SC.Student_ID, ST.ID,SC.Class_ID, SC.NumOfSessions,SC.Price," & _
               "SC.Price*SC.NumOfSessions-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As TotalstudentPriceRemaining " & _
               "  FROM Students_Tbl  AS ST " & _
               " INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID " & _
               " INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id " & _
               " INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
               " LEFT JOIN tbl_transaction As TR ON TR.Student_Class_ID=SC.ID), " & _
  "  CT      AS (SELECT SC.NumOfSessions,ST.STNom As Student, SC.Class_ID,S.Subject As Subject, TR.ID, TR.Teacher_Subject_ID, SC.Price,  T.TNom As Teacher,  " & _
              "  SC.Price*SC.NumOfSessions As studentTotal, TotalstudentPriceRemaining, " & _
               " SC.NumOfSessions, COALESCE(TR.Payment,0) AS Payment, " & _
               " TR.Paydate " & _
               " FROM tbl_subject As S " & _
               " INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID " & _
               " INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
               " LEFT JOIN SC ON SC.Class_ID=TS.ID " & _
               " LEFT JOIN Students_Tbl AS ST ON SC.Student_ID=ST.ID " & _
               " LEFT JOIN tbl_transaction As TR ON TR.Student_Class_ID=SC.ID) " & _
" SELECT  CT.Student,  CT.Class_ID,CT.Teacher_Subject_ID,CT.ID, CT.Subject, CT.NumOfSessions, CT.Price, CT.student,  CT.studentTotal, " & _
" SUM(CT.Payment) As PaymentByStudentToSchool, MIN(CT.TotalstudentPriceRemaining) As TotalstudentPriceRemaining, MAX(CT.PayDate) As PayDate " & _
" From CT where CT.TotalStudentPriceRemaining = 0 " & _
" GROUP  BY CT.Subject, CT.Class_ID, CT.student,  CT.studentTotal  "
Set Rs = Cnn.OpenRecordset(StrSql)

I can get the students who have completed their payments.
But if I want to retrive those who have not completed theit payments, I change this part of the code:

where CT.TotalStudentPriceRemaining > 0

this code retrieves the students who have paid their fees in in installments even they have completed their payments in addition to those who have not completed their payments.

[Zvoni]

WITH
    SSC    AS (SELECT SC.Class_ID, SC.Student_ID, SC.NumOfSessions, COALESCE(SC.Price,S.SubjectPrice) As SubjectPrice, 
            COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions As SubjectTotal, S.SName As Subject, 
            COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, TS.Teacher_ID, 
            COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice, 
            TR.PayDate    
            FROM tbl_student_class AS SC 
            INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
            INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
            LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID)

SELECT ST.STName As Student, SSC.Subject, SSC.SubjectPrice, T.TName As TeacherName,
SSC.NumOfSessions,
SSC.SubjectTotal,
SSC.PaymentByStudentToSchool, 
SSC.RemainingSubjectPrice,
SSC.PayDate
FROM tbl_student AS ST 
INNER JOIN SSC ON SSC.Student_ID=ST.ID
INNER JOIN tbl_teacher AS T ON T.ID=SSC.Teacher_ID 
WHERE SSC.RemainingSubjectPrice=0 /* >0 for those who haven't paid in full */
ORDER BY ST.ID, SSC.Subject, SSC.PayDate

Returns (i had to change some payments for Adam Sandler)

Student	Subject	SubjectPrice	TeacherName	NumOfSessions	SubjectTotal	PaymentByStudentToSchool	RemainingSubjectPrice	PayDate
Adam Sandler	Physics	100.0	James Cameron	1	100.0	40.0	0.0	2023-02-28

Note: It returns the LAST Payment, where it reaches RemainingSubjectPrice = 0

[newbie2]

Zvoni
I'm so grateful to you
I managed to retrieve the students whose RemainingSubjectPrice= 0 with my code in my last post.
I even tested your code and it is working perfect.
My main ptoblem was how to retrieve the students who have partly made payments but did not complete their payments.
I tried wmodifying this part

WHERE SSC.RemainingSubjectPrice>0

Unfortunately it retrieves the students who have paid their fees in in installments even they have completed their payments.
Suppose Adam Sandler 's SubjectPrice is 100
he paid two installments
100 - 50 = 50
100 - 50 = 0
Using the code below it retreives

WHERE SSC.RemainingSubjectPrice>0

Adam Sandler 100 ----- 50 ------ 50
even he has completed his payments
Thank you

[Arnoutdv]

Maybe you should start with writing down all requirements and use cases first.
Now you ask for A, you get a solution for and then you say, but I can’t get B.
Then you get an answer for B, and then you come with exception C.

[Zvoni]

Maybe you should start with writing down all requirements and use cases first.
Now you ask for A, you get a solution for and then you say, but I can’t get B.
Then you get an answer for B, and then you come with exception C.

No, he‘s right. I didn’t consider what he said.
unfortunatly, it‘ll be thursday i can have a look at it since i‘m out of office the next 2 days.

but i think it’s easy. I do have an idea, how to solve it

[newbie2]

but i think it’s easy. I do have an idea, how to solve it

You made me feel happy.
Thank you very much for your generosity

[newbie2]

Mr. Zvoni
Another requirement if possible
In addition to the code retreiving the students who have not completed their payments,
I need another code that query for those who have not completed their payments and those
who have not paid at all.
Something like:

WITH
    SSC    AS (SELECT TR.Student_Class_ID, SC.Class_ID, SC.ID, SC.Student_ID, SC.NumOfSessions, COALESCE(SC.Price,S.SubjectPrice) As SubjectPrice, 
            COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions As SubjectTotal, S.SName As Subject, 
            COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, TS.Teacher_ID, 
            COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice, 
            TR.PayDate    
            FROM tbl_student_class AS SC 
            INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
            INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
            LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID)

SELECT ST.STName As Student, SSC.Subject, SSC.SubjectPrice, T.TName As TeacherName,
SSC.NumOfSessions,
SSC.SubjectTotal,
SSC.Student_Class_ID, " & _
SSC.ID, " & 
SSC.PaymentByStudentToSchool, 
SSC.RemainingSubjectPrice,
SSC.PayDate
FROM tbl_student AS ST 
INNER JOIN SSC ON SSC.Student_ID=ST.ID
INNER JOIN tbl_teacher AS T ON T.ID=SSC.Teacher_ID 
WHERE SSC.RemainingSubjectPrice>0 or Not Exists(Select 1 from tbl_transaction Where SSC.Student_Class_ID=SSC.ID)
ORDER BY ST.ID, SSC.Subject, SSC.PayDate

[Zvoni]

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)
            
SELECT S.STName As Student, TR.SubjectName, TR.TeacherName, TR.SubjectPrice, TR.NumOfSessions, 
TR.TotalSubjectPrice, TR.SumPayment, 
TR.TotalSubjectPrice-TR.SumPayment As RemainingPayment, 
TR.LastPayDate

FROM tbl_student AS S 
LEFT JOIN TR ON TR.Student_ID=S.ID 
--WHERE 
--TR.TotalSubjectPrice-TR.SumPayment=0 /*Returns Students who have paid in full */
--TR.TotalSubjectPrice-TR.SumPayment>0 AND TR.LastPayDate IS NOT NULL /*returns Students who have not paid fully, but at least one payment */
--TR.LastPayDate IS NULL /*returns Students who haven't paid this class at all */
ORDER BY S.STName

Here you go
Note: DO NOT COMBINE THOSE WHERE-FILTERS

[newbie2]

Zvoni
Not only you are generous but you are so brillant.
Million thanks

[newbie2]

Zvoni
I deeply apologise for you and for Vbforums
Maybe this is my last requirement
I have been struggling for hours to adapt this last code on teachers' payments but without your help I'm hopeless.

StrSql = " WITH " & _
   " TR  AS (SELECT  SC.ID, TS.ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) AS SubjectPrice," & _
         " S.SName  As SubjectName, St.STName As studentname, TS.Teacher_ID, TS.TeacherPercentage, SC.NumOfSessions, " & _
          "  SubjectPrice*TS.TeacherPercentage/100 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 Students_Tbl AS St ON St.ID=SC.student_ID " & _
           " INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
           " LEFT JOIN tbl_transaction As T ON T.Teacher_Subject_ID=TS.ID " & _
           " GROUP BY T.ID, S.Subject, T.TName, St.STName, TS.TeacherPercentage,COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) " & _
" SELECT  T.TName As teacher, TR.Teacher_ID, TR.SubjectName, TR.studentname, TR.SubjectPrice, TR.NumOfSessions, " & _
" TR.TotalSubjectPrice, TR.SumPayment, " & _
" TR.TotalSubjectPrice-TR.SumPayment As RemainingPayment, " & _
" TR.LastPayDate " & _
" FROM tbl_teacher AS T " & _
" LEFT JOIN TR ON TR.Teacher_ID=T.ID " & _
" WHERE " & _
" and TR.TotalSubjectPrice-TR.SumPayment>0  " & _
" ORDER BY T.TName "

Of course I can retrieve the teachers who haven't got their full payment using the other code,
but I liked very much this code.
thank you and Deeply sorry sir

[Zvoni]

That one will definitely be Tomorrow at the earliest
(Currently my boss is looking at me from across the room)

[newbie2]

Take all your time sir
thank you very much

[Zvoni]

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage),                
    CT      AS (SELECT S.SName As SubjectName, SC.TotalPrice, T.TName As TeacherName, SC.TeacherPercentage, 
                SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
                SC.TotalSessions, SUM(COALESCE(TR.Payment,0)) AS Payment,
                MAX(TR.Paydate) As LastPayDate
                FROM tbl_subject As S 
                INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
                INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
                LEFT JOIN SC ON SC.Class_ID=TS.ID
                LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID
                GROUP BY S.SName, SC.TOtalPrice,T.TName, SC.TeacherPercentage, SC.TotalPrice*TS.TeacherPercentage/100,SC.TotalSessions)
                
SELECT CT.SubjectName, CT.TotalSessions, CT.TotalPrice, CT.TeacherName, CT.TeacherPercentage, CT.TeacherTotalPrice, 
CT.Payment As SumPayment, 
CT.TeacherTotalPrice-CT.Payment As RemainingPayment,
CT.LastPayDate
FROM CT 
--WHERE 
--CT.TeacherTotalPrice-CT.Payment=0 /*Teacher paid in full for subject */
--CT.TeacherTotalPrice-CT.Payment>0 AND CT.LastPayDate IS NOT NULL /*Teacher partialy paid for Subject */
--CT.LastPayDate IS NULL /*Teacher not paid at all for Subject */

No other way

[newbie2]

Zvoni
You are wonderful
You’re the best
Thank you million times

[newbie2]

Zvoni
I deeply feel ashamed but I'm sorry I have no other way.
I need to calculate the SumPayment of all the students who have completed their payments but I'm stuck.
When I add :
Debug.Print RS!SumPayment I get the SumPayment of the last record.
I spent nearly a whole night trying to modify the code but I get plenty of errors.

WITH
    TR  AS (SELECT SC.ID, SC.Student_ID, SC.Price AS SubjectPrice, 
            SC.NumOfSessions, 
            SC.Price*SC.NumOfSessions As TotalSubjectPrice,
            SUM(COALESCE(T.Payment,0)) As SumPayment
            FROM tbl_student_class AS SC 
            LEFT JOIN tbl_transaction As T ON T.Student_Class_ID=SC.ID 
            GROUP BY SC.ID,SC.Student_ID, Price, SC.NumOfSessions,SC.Price *SC.NumOfSessions)
            
SELECT TR.SubjectPrice, TR.NumOfSessions, 
TR.TotalSubjectPrice, TR.SumPayment, 
TR.TotalSubjectPrice-TR.SumPayment As RemainingPayment, 
TR.LastPayDate

FROM tbl_student AS S 
LEFT JOIN TR ON TR.Student_ID=S.ID 
--WHERE 
--TR.TotalSubjectPrice-TR.SumPayment=0 /*Returns Students who have paid in full */
--TR.TotalSubjectPrice-TR.SumPayment>0 AND TR.LastPayDate IS NOT NULL /*returns Students who have not paid fully, but at least one payment */
--TR.LastPayDate IS NULL /*returns Students who haven't paid this class at all */

[newbie2]

Hello sir
I want also to calculate the TeacherTotal of a specific teacher.
For this I use this query:

StrSql = "SELECT " & _
"  TR.ID, TR.Teacher_Subject_ID,SC.Price,  TS.TeacherPercentage, " & _
" SUM(SC.Price*SC.NumOfSessions)*(TS.TeacherPercentage/100) As TeacherTotal " & _
" FROM tbl_student_class As SC " & _
" INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id " & _
" INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
" LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID " & _
" Where TS.Teacher_ID = 1
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs!TeacherTotal

It is OK when a teacher has one subject to teach but if there is more than one subject , the code is giving false results.
thank you

[Zvoni]

Just to make sure i understand it correctly:
1) You need the Students/StudentTotal who have paid in full IRRESPECTIVE of the Subject
2) You need the Teachers/TeacherTotal IRRESPECTIVE of Subjects

[newbie2]

Just to make sure i understand it correctly:
1) You need the Students/StudentTotal who have paid in full IRRESPECTIVE of the Subject
2) You need the Teachers/TeacherTotal IRRESPECTIVE of Subjects

Sorry sir perhaps I was not clear enough
the first requirement:
I want to calculate the SumPayment of all students who have completed their paments.
i mean the receipts IRRESPECTIVE of the Subject
the 2nd requirement
I want to calculate theTeacherTotal of a specific teacher.
thank you

[Zvoni]

I want to calculate the SumPayment of all students who have completed their paments.
i mean the receipts IRRESPECTIVE of the Subject

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 */
--COALESCE(SP.SumTotal, 0)-COALESCE(SP.SumPayment, 0)>0 /*returns Students who have not paid fully*/
ORDER BY S.STName

the 2nd requirement
I want to calculate theTeacherTotal of a specific teacher.
thank you

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage),                
    CT      AS (SELECT S.SName As SubjectName, SC.TotalPrice, T.ID AS TeacherID, T.TName As TeacherName, SC.TeacherPercentage, 
                SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
                SC.TotalSessions, SUM(COALESCE(TR.Payment,0)) AS Payment,
                MAX(TR.Paydate) As LastPayDate
                FROM tbl_subject As S 
                INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
                INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
                LEFT JOIN SC ON SC.Class_ID=TS.ID
                LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID
                GROUP BY S.SName, SC.TOtalPrice,T.ID,T.TName, SC.TeacherPercentage, SC.TotalPrice*TS.TeacherPercentage/100,SC.TotalSessions),
     PT     AS (SELECT CT.TeacherID, SUM(CT.TeacherTotalPrice) As SumTotalTeacher, SUM(CT.Payment) As TotalTeacherPayment 
                FROM CT GROUP BY CT.TeacherID)
    
SELECT T.TName As TeacherName, COALESCE(PT.SumTotalTeacher, 0) As SumTotalTeacher, COALESCE(PT.TotalTeacherPayment, 0) As TotalTeacherPayment, 
COALESCE(PT.SumTotalTeacher, 0)-COALESCE(PT.TotalTeacherPayment, 0) As Remaining

FROM tbl_teacher AS T
LEFT JOIN PT ON PT.TeacherID=T.ID
--WHERE 
--COALESCE(PT.SumTotalTeacher, 0)-COALESCE(PT.TotalTeacherPayment, 0)=0 /*Teacher paid in full */
--COALESCE(PT.SumTotalTeacher, 0)-COALESCE(PT.TotalTeacherPayment, 0)>0 /*Teacher not paid full*/

[newbie2]

sorry Zvoni
May be there is a misunderstanding.
I want to calculate the TeacherTotal of a specific teacher.
Somehing Like I'm doing here:

StrSql = "SELECT " & _
"  TR.ID, TR.Teacher_Subject_ID,SC.Price,  TS.TeacherPercentage, " & _
" SUM(SC.Price*SC.NumOfSessions)*(TS.TeacherPercentage/100) As TeacherTotal " & _
" FROM tbl_student_class As SC " & _
" INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id " & _
" INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
" LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID " & _
" Where TS.Teacher_ID = 1
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs!TeacherTotal

[Zvoni]

sorry Zvoni
May be there is a misunderstanding.
I want to calculate the TeacherTotal of a specific teacher.
Somehing Like I'm doing here:

StrSql = "SELECT " & _
"  TR.ID, TR.Teacher_Subject_ID,SC.Price,  TS.TeacherPercentage, " & _
" SUM(SC.Price*SC.NumOfSessions)*(TS.TeacherPercentage/100) As TeacherTotal " & _
" FROM tbl_student_class As SC " & _
" INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id " & _
" INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
" LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID " & _
" Where TS.Teacher_ID = 1
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs!TeacherTotal

?!?!?!?!

WITH 
    SC    AS (SELECT SC.Class_ID, SUM(COALESCE(SC.Price, S.SubjectPrice)*SC.NumOfSessions) As TotalPrice, 
                TS.TeacherPercentage, SUM(SC.NumOfSessions) As TotalSessions
                FROM tbl_student_class As SC
                INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_id
                INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
                GROUP BY SC.Class_ID, TS.TeacherPercentage),                
    CT      AS (SELECT S.SName As SubjectName, SC.TotalPrice, T.ID AS TeacherID, T.TName As TeacherName, SC.TeacherPercentage, 
                SC.TotalPrice*TS.TeacherPercentage/100 As TeacherTotalPrice,
                SC.TotalSessions, SUM(COALESCE(TR.Payment,0)) AS Payment,
                MAX(TR.Paydate) As LastPayDate
                FROM tbl_subject As S 
                INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
                INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
                LEFT JOIN SC ON SC.Class_ID=TS.ID
                LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID
                GROUP BY S.SName, SC.TOtalPrice,T.ID,T.TName, SC.TeacherPercentage, SC.TotalPrice*TS.TeacherPercentage/100,SC.TotalSessions),
     PT     AS (SELECT CT.TeacherID, SUM(CT.TeacherTotalPrice) As SumTotalTeacher, SUM(CT.Payment) As TotalTeacherPayment 
                FROM CT GROUP BY CT.TeacherID)
    
SELECT T.TName As TeacherName, COALESCE(PT.SumTotalTeacher, 0) As SumTotalTeacher, COALESCE(PT.TotalTeacherPayment, 0) As TotalTeacherPayment, 
COALESCE(PT.SumTotalTeacher, 0)-COALESCE(PT.TotalTeacherPayment, 0) As Remaining

FROM tbl_teacher AS T
LEFT JOIN PT ON PT.TeacherID=T.ID
/*##################*/
WHERE T.ID = 1
--COALESCE(PT.SumTotalTeacher, 0)-COALESCE(PT.TotalTeacherPayment, 0)=0 /*Teacher paid in full */
--COALESCE(PT.SumTotalTeacher, 0)-COALESCE(PT.TotalTeacherPayment, 0)>0 /*Teacher not paid

[newbie2]

WHERE T.ID = 1

Ok it worked

[newbie2]

The SumPayment of the student who have completed their payments is 54000 but the code is giving 24000

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 */
--COALESCE(SP.SumTotal, 0)-COALESCE(SP.SumPayment, 0)>0 /*returns Students who have not paid fully*/
ORDER BY S.STName

Sir Did you take into acount that a teacher may have two subjects?
because it seems that I'm having false results
In this case I am just interested in the whole receipt I get from students who have completed their payments.
I am not interested in the student name, or the subject name neither the remaining

thank you

[newbie2]

Zvoni
Please look closely at the code.
Certainly there something wrong but I couldn't find it.
Please examine this scenario
English SumPayment = 30
Physics SumPayment = 50
Maths SumPayment = 20
English SumPayment = 30
The sum should be 130
But the code gives 100
The subject "English" is duplicated, thus the code doesn't take that into account

[Zvoni]

Will be thursday, since i‘m traveling the next two days

[newbie2]

I wish you safety road

[Zvoni]

OK, got a few minutes to look at it

Students

The first query above returns this (for the Values i have)

Student	SubjectName	TeacherName	SubjectPrice	NumOfSessions	TotalSubjectPrice	SumPayment	RemainingPayment	LastPayDate
Adam Sandler	Physics	James Cameron	100.0	1	100.0	100.0	0.0	2023-02-28
Adam Sandler	Math	Steven Spielberg	200.0	2	400.0	260.0	140.0	2024-02-25
Adam Sandler	English	Stanley Kubrick	300.0	1	300.0	60.0	240.0	2023-01-24
Elijah Wood	Physics	Steven Spielberg	100.0	1	100.0	15.0	85.0	2023-02-25
Elijah Wood	English	James Cameron	300.0	2	600.0	0	600.0
Nicole Kidman	English	James Cameron	150.0	8	1200.0	30.0	1170.0	2023-02-09

As you can see,
Adam Sandler has a total of 800 (Sum of TotalSubjectPrice) and 420 of TotalPayment
Elijah Wood has a Total of 700, and 15 of TotalPayment
Nicole Kidman has a Total of 1200 and 30 of TotalPayment

My Last Student-Query returns this

Adam Sandler	800.0	420.0	380.0
Elijah Wood	700.0	15.0	685.0
Nicole Kidman	1200.0	30.0	1170.0

So that one's correct

Teachers:

The Query to return Teachers Payment returns this

SubjectName	TotalSessions	TotalPrice	TeacherName	TeacherPercentage	TeacherTotalPrice	SumPayment	RemainingPayment	LastPayDate
English	1	300.0	Stanley Kubrick	50.0	150.0	0	150.0
English	10	1800.0	James Cameron	25.0	450.0	375.0	75.0	2023-03-08
Math	2	400.0	Steven Spielberg	75.0	300.0	300.0	0.0	2023-03-17
Physics	1	100.0	James Cameron	50.0	50.0	50.0	0.0	2023-03-15
Physics	1	100.0	Steven Spielberg	25.0	25.0	0	25.0

S. Kubrick has a Total of 150, with 0 as Payment received
J. Cameron has a Total of 500 with 425 as Payment received
S. Spielberg has a Total of 325, with 300 as Payment received

My last Query returns this

TeacherName	SumTotalTeacher	TotalTeacherPayment	Remaining
James Cameron	500.0	425.0	75.0
Steven Spielberg	325.0	300.0	25.0
Stanley Kubrick	150.0	0	150.0

So i don't know what you are talking about

[newbie2]

Zvoni
I think there is still misunderstanding.
As you can see in the grids above:
Some teachers have received completely their payments while others not.
I want to get the total "sumpayment " of the teachers whose remaining = 0.

In our case:
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)

What I need to get is:
The total "SumPayment " of the teachers whose remaining = 0 is 350
I want a query that allows me to get 350 when I debug.print "TotalSumPayment"

Same thing for students if possible.

I hope I'm clear enough this time.

[Zvoni]

Zvoni
I think there is still misunderstanding.
As you can see in the grids above:
Some teachers have received completely their payments while others not.
I want to get the total "sumpayment " of the teachers whose remaining = 0.

In our case:
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)

What I need to get is:
The total "SumPayment " of the teachers whose remaining = 0 is 350
I want a query that allows me to get 350 when I debug.print "TotalSumPayment"

Same thing for students if possible.

I hope I'm clear enough this time.

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