Page 3 of 4 FirstFirst 1234 LastLast
Results 81 to 120 of 131

Thread: Another complicated query but perhaps possible

  1. #81
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Wait a second: are you doing this on an InMemory-Database?
    IIRC, there are some exceptions for InMemory databases
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #82

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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

  3. #83

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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.
    Code:
    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
    Code:
    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)

  4. #84
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    I would need the initial Values to check that (prices, payments etc.)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #85

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    I would need the initial Values to check that (prices, payments etc.)
    Is there something I would do?

  6. #86
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Is there something I would do?
    I need the "same" INSERT's (Values) you use to check that
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #87

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    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

  8. #88
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Ok, will use the very first sample data, and adjust accordingly.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #89
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

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

    DDL
    Code:
    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
    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #90

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Million thanks sir

  11. #91

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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.
    Code:
    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

  12. #92

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

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

    Code:
    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:
    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.
    Last edited by newbie2; Mar 16th, 2024 at 06:38 PM.

  13. #93
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #94

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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
    Code:
    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
    Code:
    WHERE SSC.RemainingSubjectPrice>0
    Adam Sandler 100 ----- 50 ------ 50
    even he has completed his payments
    Thank you

  15. #95
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,987

    Re: Another complicated query but perhaps possible

    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.

  16. #96
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Arnoutdv View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #97

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post

    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

  18. #98

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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:

    Code:
    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

  19. #99
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    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)
                
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #100

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

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

  21. #101

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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.

    Code:
    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
    Last edited by newbie2; Mar 19th, 2024 at 08:37 PM.

  22. #102
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    That one will definitely be Tomorrow at the earliest
    (Currently my boss is looking at me from across the room)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  23. #103

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Take all your time sir
    thank you very much

  24. #104
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  25. #105

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

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

  26. #106

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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.
    Code:
    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 */
    Last edited by newbie2; Mar 23rd, 2024 at 08:38 PM.

  27. #107

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

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

    Code:
    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

  28. #108
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  29. #109

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    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

  30. #110
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    I want to calculate the SumPayment of all students who have completed their paments.
    i mean the receipts IRRESPECTIVE of the Subject
    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 */
    --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
    Code:
    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*/
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  31. #111

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    sorry Zvoni
    May be there is a misunderstanding.
    I want to calculate the TeacherTotal of a specific teacher.
    Somehing Like I'm doing here:
    Code:
    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

  32. #112
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    sorry Zvoni
    May be there is a misunderstanding.
    I want to calculate the TeacherTotal of a specific teacher.
    Somehing Like I'm doing here:
    Code:
    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
    ?!?!?!?!
    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  33. #113

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    Code:
    WHERE T.ID = 1
    Ok it worked
    Last edited by newbie2; Mar 25th, 2024 at 11:49 AM.

  34. #114

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

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

    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 */
    --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
    Last edited by newbie2; Mar 25th, 2024 at 02:20 PM.

  35. #115

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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

  36. #116
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Will be thursday, since i‘m traveling the next two days
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  37. #117

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    I wish you safety road

  38. #118
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  39. #119

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    929

    Re: Another complicated query but perhaps possible

    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.

  40. #120
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,515

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Page 3 of 4 FirstFirst 1234 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width