Page 2 of 4 FirstFirst 1234 LastLast
Results 41 to 80 of 131

Thread: Another complicated query but perhaps possible

  1. #41

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    thank you sir for your crystal explanation.
    I understand quite well your concept.
    However in the case of the school to which I'm trying to help them with this application , the concept is different.
    I have already talked to the school headmaster.
    The school is paying the teacher from the incomes or receipts gathered from students' fees.
    For example:
    The school has gathered only 30 $ for James Cameron (10 +10 +10) for physics.
    If they want to pay him right now, they calculate his payment based on this sum.
    30 * 50 %*NumOfSessions.
    The school is not going to pay him out of the incomes received from students for a specific subject and specific teacher.
    I wish you would understand me.
    thank you

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

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    thank you sir for your crystal explanation.
    I understand quite well your concept.
    However in the case of the school to which I'm trying to help them with this application , the concept is different.
    I have already talked to the school headmaster.
    The school is paying the teacher from the incomes or receipts gathered from students' fees.
    For example:
    The school has gathered only 30 $ for James Cameron (10 +10 +10) for physics.
    If they want to pay him right now, they calculate his payment based on this sum.
    30 * 50 %*NumOfSessions.
    The school is not going to pay him out of the incomes received from students for a specific subject and specific teacher.
    I wish you would understand me.
    thank you
    If the School only pays the teacher from the Student-Payments as they come in, then NumOfSessions has no influence on the PAYMENT at all, only for the Calculation of the Total

    the School has gathered 30$ from 3 Payments from a student(10+10+10).
    That PAYMENT doesn't know how many Sessions the student has booked!

    the teacher gets 15 (5+5+5) --> 50% of the Payment

    The NumOfSessions has no influence whatsoever on the PAYMENT the teacher receives, only on the Total!

    Imagine a student books only one session for 100$, but pays in 5 installments (20$ each)
    Student pays 20$ on monday, teacher receives 10$ on Thursday
    Student pays 20$ on next monday, teacher receives 10$ on next Thursday.
    and so on.....

    No NumOfSessions whatsoever pertinent to PAYMENTS.
    Only to calculation of totals
    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

  3. #43

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    If the School only pays the teacher from the Student-Payments as they come in, then NumOfSessions has no influence on the PAYMENT at all, only for the Calculation of the Total
    you're completely right sir

  4. #44

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    I think no escape of a second trasaction table.

  5. #45

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Let's take a single Subject (Physics), which the school says: "One session costs 100$"
    In this case, Can a student negociate the price of a subject?

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

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    In this case, Can a student negociate the price of a subject?
    Yes, since we left that field in student_class.
    We‘d just have to add the NumOfSessions to calculate the total.
    would have to look again at the query
    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. #47
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,541

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    I think no escape of a second trasaction table.
    You are thinking the wrong way.
    You said, this Program/Database is to help that School. Then you have to think like that School. You HAVE TO BE that School.
    And the School looks at this (single) Transaction-table like if it's their Bank-Account:
    "Hey, we have payments coming in from Students, and we have payments going out to teachers"

    Quote Originally Posted by newbie2 View Post
    In this case, Can a student negociate the price of a subject?
    If a Student negotiates an individual price, what's the teacher getting:
    his ratio ("50%") of the "individual" Student's price, or the "original" Subject-Price?
    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

  8. #48

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    If a Student negotiates an individual price, what's the teacher getting:
    his ratio ("50%") of the "individual" Student's price, or the "original" Subject-Price?
    The answer is : his ratio ("50%") of the "individual" Student's price
    As explained so far:
    The school is not going to spend any money out of the students'fees.
    It 's very complicated as described in the title of the post.

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

    Re: Another complicated query but perhaps possible

    In tbl_student_class change Field Price to 40 for ID=1 (Adam Sandler pays 40$ for Physics instead of 100$)

    New query for Student rates
    Code:
    SELECT ST.STName As Student, S.SName As Subject, COALESCE(SC.Price,S.SubjectPrice) As SubjectPrice, T.TName As TeacherName,
    SC.NumOfSessions,
    COALESCE(SC.Price,S.SubjectPrice)*SC.NumOfSessions As SubjectTotal,
    COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
    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 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
    INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
    LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID 
    ORDER BY St.ID, SC.ID, TR.PayDate
    Returns
    Student Subject SubjectPrice TeacherName NumOfSessions SubjectTotal PaymentByStudentToSchool RemainingSubjectPrice PayDate
    Adam Sandler Physics 40.0 James Cameron 1 40.0 10.0 30.0 2023-01-10
    Adam Sandler Physics 40.0 James Cameron 1 40.0 10.0 20.0 2023-02-12
    Adam Sandler Physics 40.0 James Cameron 1 40.0 10.0 10.0 2023-02-28
    Adam Sandler Math 200.0 Steven Spielberg 2 400.0 20.0 380.0 2023-02-06
    Adam Sandler English 300.0 Stanley Kubrick 1 300.0 60.0 240.0 2023-01-24
    Elijah Wood Physics 100.0 Steven Spielberg 1 100.0 15.0 85.0 2023-02-25
    Elijah Wood English 300.0 James Cameron 2 600.0 0 600.0
    Nicole Kidman English 300.0 James Cameron 3 900.0 30.0 870.0 2023-02-09
    Viggo Mortensen Physics 100.0 James Cameron 2 200.0 0 200.0


    New Query for teachers Payments
    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)
    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

    Physics with James Cameron consists of 2 Sessions for 100$ (Viggo Mortensen) and 1 Session for 40$ (Adam Sandler)
    sum = 240$ --> Teacher gets 50% of that

    Returns
    SubjectName TotalPrice TeacherName TeacherPercentage TeacherTotalPrice TotalSessions Payment TotalTeacherPriceRemaining PayDate
    Physics 240.0 James Cameron 50.0 120.0 3 30.0 90.0 2023-03-05
    Physics 240.0 James Cameron 50.0 120.0 3 25.0 65.0 2023-03-15
    English 1500.0 James Cameron 25.0 375.0 5 30.0 345.0 2023-02-15
    Math 400.0 Steven Spielberg 75.0 300.0 2 0 300.0
    English 300.0 Stanley Kubrick 50.0 150.0 1 0 150.0
    Physics 100.0 Steven Spielberg 25.0 25.0 1 0 25.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

  10. #50

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Zvoni
    Thank you very very much
    I'm so grateful to you
    I'll keep the thread open for some days
    If I meet a problem, I'll come back.

  11. #51

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    I have created a new table (Payment_tbl)
    In this table I mark all the transactions that are complete.
    If the sum(payments) in the (transaction_tbl) = the price in (the tbl_student_class),
    I set "Paid" = 1 for the corresponding transaction in (the payment_tbl).
    I tried this query but did not succeed.
    Code:
    Update Payment_tbl Set Due = 1 " & _
    " FROM (SELECT sum(Payment) as Payment,Price FROM tbl_transaction  " & _
    " LEFT JOIN tbl_student_class ON tbl_transaction.Student_Class_ID = tbl_student_class.ID  " & _
    " WHERE   Payment = Price)"
    Thank you

  12. #52

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    I was able to solve this ussue by a workaroud.
    Instead of creating a new table, I added a column "Paid" in "tbl_student_class" table.
    then I used this code:
    Code:
    StrSql = "Select sum(Payment) as Payment,tbl_student_class.Price FROM tbl_transaction  " & _
    " LEFT JOIN tbl_student_class ON tbl_transaction.Student_Class_ID = tbl_student_class.ID " & _
    " where Student_Class_ID = " & txt_ID.Text
    Set Rs = Cnn.OpenRecordset(StrSql)
    If Rs!Payment = Rs!Price Then
    Cnn.Execute "Update tbl_student_class Set paid  = 1 WHERE ID = " & txt_ID.Text
    End If
    It worked

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

    Re: Another complicated query but perhaps possible

    How to update everything in one go.
    Even respects if Student has no "individual" Price but the regular SubjectPrice

    Code:
    WITH    
        CTE AS (SELECT SC.ID, COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment,
                (C ASE WHEN COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid
                FROM tbl_student_class AS SC 
                INNER JOIN (SELECT ID, SubjectPrice FROM tbl_subject) As PR ON PR.ID=SC.Class_ID
                LEFT JOIN (SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID) As TR 
                ON TR.Student_Class_ID=SC.ID) 
    
    UPDATE tbl_student_class As TSC
    SET Paid=CTE.CalcPaid 
    FROM CTE 
    WHERE CTE.ID=TSC.ID
    BEWARE: Due to Forum-Issues i had to insert a SPACE in the word C ASE above
    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. #54
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,541

    Re: Another complicated query but perhaps possible

    Whooops!

    NumOFSessions was missing above
    Code:
     	WITH    
        CTE AS (SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment,
                (C ASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid
                FROM tbl_student_class AS SC 
                INNER JOIN (SELECT ID, SubjectPrice FROM tbl_subject) As PR ON PR.ID=SC.Class_ID
                LEFT JOIN (SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID) As TR 
                ON TR.Student_Class_ID=SC.ID) 
    
    UPDATE tbl_student_class As TSC
    SET Paid=CTE.CalcPaid 
    FROM CTE 
    WHERE CTE.ID=TSC.ID
    And again the blank in C ASE because of Forum-Problems
    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

  15. #55

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Zvoni
    Thank you very much for the very sophisticated code .
    It worked like a charm.
    thank you again
    Last edited by newbie2; Feb 19th, 2024 at 12:05 PM.

  16. #56

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    You are so generous
    Million thanks sir

  17. #57

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Zvoni Excuse me
    I'm getting crasy to make the last code working.
    I spent a whole night trying to understand the code.
    Code:
    StrSql = "WITH " & _
       " CTE AS (SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, " & _
              "  (CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid " & _
               " FROM tbl_student_class AS SC " & _
               " INNER JOIN (SELECT ID, SubjectPrice FROM tbl_subject) As PR ON PR.ID=SC.Class_ID " & _
               " LEFT JOIN (SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID) As TR " & _
               " ON TR.Student_Class_ID=SC.ID) " & _
    " UPDATE tbl_student_class As TSC " & _
    " Set Paid = CTE.CalcPaid " & _
    " From CTE " & _
    " Where CTE.ID = TSC.ID "
     Set Rs = Cnn.OpenRecordset(StrSql)
    Everything seems OK except the last line.
    Please what is CTE.ID?
    Is it tbl_student_class.Class_ID?
    And what is TSC.ID ?
    How can I check the price and the sum of payments by means of "Debug.print"?
    Yesterday everything was working but today I could not get the code work properly.
    I made the price equal to the sum payments then I find some records are updated (Paid = 1) while others are not (Paid = 0).
    thank you

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

    Re: Another complicated query but perhaps possible

    Right.

    CTE = Common Table Expression
    Think of it like a temporary VIEW or table the query creates on the fly while executing that query

    As for debugging:
    Split out the inner SELECT of the CTE

    Kind of (note again the blank in C ASE )
    Aircode
    Code:
    Dim sCTE As String
    Dim sLJ As String  'For the LEFT JOIN
    dim sIJ As String 'For the INNER JOIN
    Dim SQL As String
    
    SQL="WITH CTE AS("
    
    sCTE="SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
    sCTE=sCTE & "(C ASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "
    
    sIJ="SELECT ID, SubjectPrice FROM tbl_subject"
    
    sCTE=sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "
    
    sLJ="SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"
    
    sCTE=sCTE & "LEFT JOIN (" & sLJ & ") As TR "
    sCTE=sCTE & "ON TR.Student_Class_ID=SC.ID) "
    SQL=SQL & sCTE & "UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    
    Set Rs=Cnn.OpenRecordSet(sIJ)  'We're only executing the INNER JOIN-Part!!!
    Debug.Print Rs("ID")
    Debug.Print Rs("SubjectPrice")
    
    'If OK, then execute the LEFT JOIN
    Set Rs=Cnn.OpenRecordSet(sLJ)  'We're only executing the LEFT JOIN-Part!!!
    Debug.Print Rs("Student_Class_ID")
    Debug.Print Rs("Payment")
    
    'If OK, then execute the CTE
    Set Rs=Cnn.OpenRecordSet(sCTE)  'We're only executing the CTE!!!
    Debug.Print Rs("ID")
    Debug.Print Rs("StudentPrice")  'This is NumOfSessions multiplied with either individual StudentPrice or SubjectPrice
    Debug.Print Rs("Payment")
    Debug.Print Rs("CalcPaid")
    
    'If everything OK, execute everything
    Set Rs=Cnn.OpenRecordSet(SQL)
    As for why it returned 0 instead of 1: Could be because those are floating-point-values. Might have to introduce a rounding
    Or you have a BLANK in Student-Price instead of a real NULL. But there is a solution, too, to catch those
    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

  19. #59

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Zvoni
    I'm getting error in misplacing the bracket ")"
    Cannot compile Select statement ")" : syntax error

    Code:
    Dim sCTE As String
    Dim sLJ As String  'For the LEFT JOIN
    Dim sIJ As String 'For the INNER JOIN
    Dim SQL As String
    SQL = "WITH CTE AS("
    sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
    sCTE = sCTE & "(CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "
    sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"
    sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "
    sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"
    sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
    sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID ) "
    SQL = SQL & sCTE & "UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    Set Rs = Cnn.OpenRecordset(sIJ) 'We're only executing the INNER JOIN-Part!!!
    Debug.Print Rs("ID")
    Debug.Print Rs("SubjectPrice")
    'If OK, then execute the LEFT JOIN
    Set Rs = Cnn.OpenRecordset(sLJ) 'We're only executing the LEFT JOIN-Part!!!
    Debug.Print Rs("Student_Class_ID")
    Debug.Print Rs("Payment")
    'If OK, then execute the CTE
    Set Rs = Cnn.OpenRecordset(sCTE) 'We're only executing the CTE!!!
    Debug.Print Rs("ID")
    Debug.Print Rs("StudentPrice")  'This is NumOfSessions multiplied with either individual StudentPrice or SubjectPrice
    Debug.Print Rs("Payment")
    Debug.Print Rs("CalcPaid")
    'If everything OK, execute everything
    Set Rs = Cnn.OpenRecordset(SQL)

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

    Re: Another complicated query but perhaps possible

    Found it

    Code:
    Dim sCTE As String
    Dim sLJ As String  'For the LEFT JOIN
    Dim sIJ As String 'For the INNER JOIN
    Dim SQL As String
    
    SQL = "WITH CTE AS("
    
    sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
    sCTE = sCTE & "(C ASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "
    
    sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"
    
    sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "
    
    sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"
    
    sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
    sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID" 'HERE THE CLOSING BRACKET REMOVED
    
    'Here the Closing Bracket inserted
    SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    Note again the BLANK in C ASE above
    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

  21. #61

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    I'm sorry sir I'm still having trouble.
    I can't figure out where the problem is.
    Code:
    SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    Set Rs = Cnn.OpenRecordset(sLJ)
    Debug.Print Rs("Student_Class_ID")
    Debug.Print Rs("Payment")
    Set Rs = Cnn.OpenRecordset(sCTE)
    Debug.Print Rs("ID")
    Debug.Print Rs("StudentPrice")
    Debug.Print Rs("CalcPaid")
    Set Rs = Cnn.OpenRecordset(SQL)
    This is the output:
    100
    1
    100
    0

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

    Re: Another complicated query but perhaps possible

    Which (Sub-) Query, and which Fields?
    What are the Values for the other Fields?
    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. #63

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Code:
       Dim sSQL As String
     DBFile = App.path & "\Example.db"
     Set Cnn = New RC6.cConnection
    
        If Dir(DBFile, vbNormal) = vbNullString Then
            If Cnn.CreateNewDB(DBFile) = False Then
                MsgBox "Failed to create new sqlite database !" & _
                            vbCrLf & vbCrLf & Cnn.LastDBError
    
        End If
        End If
    '
    
     Set Cnn = New_c.Connection(DBFile)
    With Cnn.NewFieldDefs
    .Add "ID   Integer Primary Key"
     .Add "STName   TEXT "
     .Add "Sfees   Double"
    End With
    Cnn.CreateTable "tbl_student"
    '
    With Cnn.NewFieldDefs
    .Add "ID    INTEGER Primary Key"
     .Add "SName    TEXT"
    .Add "SubjectPrice    REAL DEFAULT NULL"
    End With
    Cnn.CreateTable "tbl_subject"
    '
     With Cnn.NewFieldDefs
    .Add "ID    INTEGER Primary Key"
     .Add "TName    TEXT NOT NULL"
     End With
    Cnn.CreateTable "tbl_teacher"
    ''
    With Cnn.NewFieldDefs
      .Add "ID    INTEGER Primary Key "
      .Add "Teacher_ID    INTEGER  "
      .Add "Subject_ID    INTEGER"
      .Add "TeacherPercentage    REAL NOT NULL"
      .Add "ClassPrice    REAL DEFAULT NULL," & _
       " 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"
    End With
    Cnn.CreateTable "tbl_teacher_subject"
    '
    With Cnn.NewFieldDefs
      .Add "ID    INTEGER PRIMARY KEY"
      .Add "Student_ID    INTEGER  "
      .Add "Class_ID    INTEGER"
      .Add "Price    REAL DEFAULT NULL "
      .Add "Paid    INTEGER"
      .Add "NumOfSessions INTEGER DEFAULT 1," & _
      " 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"
    End With
    Cnn.CreateTable "tbl_student_class"
    
    With Cnn.NewFieldDefs
       .Add "ID   INTEGER PRIMARY KEY "
        .Add "Payment    REAL "
        .Add "PayDate    TEXT "
        .Add "Student_Class_ID   INTEGER DEFAULT NULL "
        .Add "Teacher_Subject_ID    INTEGER DEFAULT NULL ," & _
        " 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))"
    End With
    Cnn.CreateTable "tbl_transaction"
    
    Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('1', 'Adam Sandler')"
    Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('2', 'Elijah Wood')"
    Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('3', 'Nicole Kidman')"
    
    Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('1', 'James Cameron')"
    Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('2', 'Steven Spielberg')"
    Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('3', 'Stanley Kubrick')"
    '
    Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('1', 'Physics', '100.0')"
    Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('2', 'Math', '200.0')"
    Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('3', 'English', '300.0')"
    
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('1', '1', '1', '50.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('2', '1', '3', '25.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('3', '2', '2', '75.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('4', '3', '3', '50.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('5', '2', '1', '25.0')"
    
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('1', '1', '1', 10,1)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('2', '1', '3', 20,2)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('3', '2', '5', 30,1)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('4', '3', '2', 40,3)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('5', '1', '4', 50,1)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('6', '2', '2', 60,2)"
    
    
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('1', '50.0', '2023-01-10', '1', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('2', '70.0', '2023-02-06', '2', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('3', '80.0', '2023-02-09', '4', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('4', '5.0', '2023-02-12', '1', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('5', '60.0', '2023-02-25', '3', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('6', '100.0', '2023-02-28', '1', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('7', '80.0', '2023-01-24', '5', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('8', '30.0', '2023-03-05', NULL, '1');"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('9', '30.0', '2023-02-15', NULL, '2');"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('10', '25.0', '2023-03-15', NULL, '1');"
    
    Dim sCTE As String
    Dim sLJ As String
    Dim sIJ As String
    Dim SQL As String
    
    SQL = "WITH CTE AS("
    
    sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
    sCTE = sCTE & "(CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "
    
    sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"
    
    sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "
    
    sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"
    
    sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
    sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID"
    
    SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    Set Rs = Cnn.OpenRecordset(sLJ)
    Debug.Print Rs("Student_Class_ID")
    Debug.Print Rs("Payment")
    Set Rs = Cnn.OpenRecordset(sCTE)
    Debug.Print Rs("ID")
    Debug.Print Rs("StudentPrice")
    Debug.Print Rs("CalcPaid")
    Set Rs = Cnn.OpenRecordset(SQL)

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

    Re: Another complicated query but perhaps possible

    And which one comes out "wrong"?
    That's the sample setup of the database i posted earlier
    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. #65

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Code:
    Dim sSQL As String
     DBFile = App.path & "\Example2.db"
     Set Cnn = New RC6.cConnection
    
        If Dir(DBFile, vbNormal) = vbNullString Then
            If Cnn.CreateNewDB(DBFile) = False Then
                MsgBox "Failed to create new sqlite database !" & _
                            vbCrLf & vbCrLf & Cnn.LastDBError
    
        End If
        End If
    '
    
     Set Cnn = New_c.Connection(DBFile)
    With Cnn.NewFieldDefs
    .Add "ID   Integer Primary Key"
     .Add "STName   TEXT "
     .Add "Sfees   Double"
    End With
    Cnn.CreateTable "tbl_student"
    '
    With Cnn.NewFieldDefs
    .Add "ID    INTEGER Primary Key"
     .Add "SName    TEXT"
    .Add "SubjectPrice    REAL DEFAULT NULL"
    End With
    Cnn.CreateTable "tbl_subject"
    '
     With Cnn.NewFieldDefs
    .Add "ID    INTEGER Primary Key"
     .Add "TName    TEXT NOT NULL"
     End With
    Cnn.CreateTable "tbl_teacher"
    ''
    With Cnn.NewFieldDefs
      .Add "ID    INTEGER Primary Key "
      .Add "Teacher_ID    INTEGER  "
      .Add "Subject_ID    INTEGER"
      .Add "TeacherPercentage    REAL NOT NULL"
      .Add "ClassPrice    REAL DEFAULT NULL," & _
       " 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"
    End With
    Cnn.CreateTable "tbl_teacher_subject"
    '
    With Cnn.NewFieldDefs
      .Add "ID    INTEGER PRIMARY KEY"
      .Add "Student_ID    INTEGER  "
      .Add "Class_ID    INTEGER"
      .Add "Price    REAL DEFAULT NULL "
      .Add "Paid    INTEGER"
      .Add "NumOfSessions INTEGER DEFAULT 1," & _
      " 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"
    End With
    Cnn.CreateTable "tbl_student_class"
    
    With Cnn.NewFieldDefs
       .Add "ID   INTEGER PRIMARY KEY "
        .Add "Payment    REAL "
        .Add "PayDate    TEXT "
        .Add "Student_Class_ID   INTEGER DEFAULT NULL "
        .Add "Teacher_Subject_ID    INTEGER DEFAULT NULL ," & _
        " 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))"
    End With
    Cnn.CreateTable "tbl_transaction"
    
    Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('1', 'Adam Sandler')"
    Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('2', 'Elijah Wood')"
    Cnn.Execute "INSERT INTO tbl_student (ID, STName) VALUES ('3', 'Nicole Kidman')"
    
    Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('1', 'James Cameron')"
    Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('2', 'Steven Spielberg')"
    Cnn.Execute "INSERT INTO tbl_teacher (ID, TName) VALUES ('3', 'Stanley Kubrick')"
    '
    Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('1', 'Physics', '100.0')"
    Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('2', 'Math', '200.0')"
    Cnn.Execute "INSERT INTO tbl_subject (ID, SName, SubjectPrice) VALUES ('3', 'English', '300.0')"
    
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('1', '1', '1', '50.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('2', '1', '3', '25.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('3', '2', '2', '75.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('4', '3', '3', '50.0')"
    Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('5', '2', '1', '25.0')"
    
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid, NumOfSessions) VALUES ('1', '1', '1', 85,NULL,1)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('2', '1', '3', 70,NULL,2)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('3', '2', '5', 80,NULL,1)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('4', '3', '2', 40,NULL,3)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('5', '1', '4', 50,NULL,1)"
    Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,Paid,NumOfSessions) VALUES ('6', '2', '2', 60,NULL,2)"
    
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('1', '50.0', '2023-01-10', '1', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('2', '70.0', '2023-02-06', '2', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('3', '80.0', '2023-02-09', '4', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('4', '50.0', '2023-02-12', '1', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('5', '60.0', '2023-02-25', '3', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('6', '100.0', '2023-02-28', '1', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('7', '80.0', '2023-01-24', '5', NULL);"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('8', '30.0', '2023-03-05', NULL, '1');"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('9', '30.0', '2023-02-15', NULL, '2');"
    Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('10', '25.0', '2023-03-15', NULL, '1');"
    
    Dim sCTE As String
    Dim sLJ As String
    Dim sIJ As String
    Dim SQL As String
    
    SQL = "WITH CTE AS("
    
    sCTE = "SELECT SC.ID, SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice) As StudentPrice, COALESCE(TR.Payment, 0) As Payment, "
    sCTE = sCTE & "(CASE WHEN SC.NumOfSessions*COALESCE(SC.Price,PR.SubjectPrice)=COALESCE(TR.Payment, 0) THEN 1 ELSE 0 END) As CalcPaid "
    
    sIJ = "SELECT ID, SubjectPrice FROM tbl_subject"
    
    sCTE = sCTE & "FROM tbl_student_class AS SC INNER JOIN (" & sIJ & ") As PR ON PR.ID=SC.Class_ID "
    
    sLJ = "SELECT Student_Class_ID, SUM(Payment) As Payment FROM tbl_transaction GROUP BY Student_Class_ID"
    
    sCTE = sCTE & "LEFT JOIN (" & sLJ & ") As TR "
    sCTE = sCTE & "ON TR.Student_Class_ID=SC.ID"
    
    SQL = SQL & sCTE & ") UPDATE tbl_student_class As TSC SET Paid=CTE.CalcPaid FROM CTE WHERE CTE.ID=TSC.ID"
    Set Rs = Cnn.OpenRecordset(sLJ)
    Debug.Print Rs("Student_Class_ID")
    Debug.Print Rs("Payment")
    Set Rs = Cnn.OpenRecordset(sCTE)
    Debug.Print Rs("ID")
    Debug.Print Rs("StudentPrice")
    Debug.Print Rs("CalcPaid")
    Set Rs = Cnn.OpenRecordset(SQL)
    Last edited by newbie2; Feb 21st, 2024 at 05:55 AM.

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

    Re: Another complicated query but perhaps possible

    I just took your latest values.

    Query to show students returns this:
    Student Subject SubjectPrice TeacherName NumOfSessions SubjectTotal PaymentByStudentToSchool RemainingSubjectPrice PayDate
    Adam Sandler Physics 10.0 James Cameron 1 10.0 50.0 -40.0 2023-01-10
    Adam Sandler Physics 10.0 James Cameron 1 10.0 5.0 -45.0 2023-02-12
    Adam Sandler Physics 10.0 James Cameron 1 10.0 100.0 -145.0 2023-02-28
    Adam Sandler Math 20.0 Steven Spielberg 2 40.0 70.0 -30.0 2023-02-06
    Adam Sandler English 50.0 Stanley Kubrick 1 50.0 80.0 -30.0 2023-01-24
    Elijah Wood Physics 30.0 Steven Spielberg 1 30.0 60.0 -30.0 2023-02-25
    Elijah Wood English 60.0 James Cameron 2 120.0 0 120.0
    Nicole Kidman English 40.0 James Cameron 3 120.0 80.0 40.0 2023-02-09


    Where in blazes is the "100" coming from?
    And this is the result of just the CTE from above
    ID StudentPrice Payment CalcPaid
    1 10.0 155.0 0
    2 40.0 70.0 0
    4 120.0 80.0 0
    6 120.0 0 0

    Where did you expect Paid=1
    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

  27. #67

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    The the "100" is coming from the table subject
    I sent the wrong code.
    I fact in my original code, the student price is set to null
    I think I need some time to concentrate carefully.
    Perhaps due to my tiredness I'm doing such mistakes.
    I'm sorry sir for disturbing you.

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

    Re: Another complicated query but perhaps possible

    btw: It might be worth considering changing the equal-comparison within the C ASE-Block from "=" to "<="
    Meaning: If the total payment exceeds the total SubjectPrice (or StudentPrice) to set the Record to Paid=1
    (for whatever reason the total Payment might be higher than the total price)
    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. #69

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    I need to retrieve the teachers who have completely received their pays.
    For that I used this code but it doesn't seem to work properly.
    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)
    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 Where  TeacherTotalPrice= Payment"

    Then I used this code but it is sending error:
    (Misuse og aggeegate sum)
    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)
    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,
    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 Where  TeacherTotalPrice= Payment"
    Am I doing something wrong?
    thank you
    Last edited by newbie2; Feb 24th, 2024 at 01:26 AM.

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

    Re: Another complicated query but perhaps possible

    Will be on monday
    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. #71

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    Will be on monday
    Thank you very much sir

  32. #72

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    I think I got it but I need your confirmation
    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)
    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,
    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  TeacherTotalPrice= Payment

  33. #73

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    I've just noticed an issue.
    I think that the "tbl_teacher_subject" should have the "ID" field AUTOINCREMENT .
    Let's imagine this scenario:
    Teacher "X" has been attributed (Class_ID 2 and 3) in tbl_student_class"
    Then Teacher "X" is deleted in "tbl_teacher_subject" and a teacher "Y" is added.
    Teacher "Y" is going to take the "ID" of Teacher "X".
    The result is that the ( class_ID 2 and 3) are attributed to teacher "Y".
    Thus, I think the only solution is to set the "ID" field AUTOINCREMENT in "tbl_teacher_subject".
    What do you think sir?
    thank you

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

    Re: Another complicated query but perhaps possible

    To get the first thing out of the way (Teacher is paid full).

    You're missing that you already get the information with the Query for the teachers payment!

    You compare Sum(Payment) against TotalPrice. Why?

    I compare TotalTeacherPriceRemaining =0 (or <=0) <-- you already get the "Remaining" shipped with the first 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 (i changed my data in tbl_student_class ID=1 to get to zero)

    SubjectName TotalPrice TeacherName TeacherPercentage TeacherTotalPrice SumPayment TotalTeacherPriceRemaining PayDate
    Physics 110.0 James Cameron 50.0 55.0 55.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

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

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Hello Zvoni
    I've just noticed an issue.
    I think that the "tbl_teacher_subject" should have the "ID" field AUTOINCREMENT .
    Let's imagine this scenario:
    Teacher "X" has been attributed (Class_ID 2 and 3) in tbl_student_class"
    Then Teacher "X" is deleted in "tbl_teacher_subject" and a teacher "Y" is added.
    Teacher "Y" is going to take the "ID" of Teacher "X".
    The result is that the ( class_ID 2 and 3) are attributed to teacher "Y".
    Thus, I think the only solution is to set the "ID" field AUTOINCREMENT in "tbl_teacher_subject".
    What do you think sir?
    thank you
    hmm.....you're right. There is an Edge-Case where you need AUTOINCREMENT, and that is if the last entry gets deleted.

    BUT!!
    If you look closely at my CREATE TABLE-Statement for tbl_teacher_subject

    Code:
    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
    )
    you will notice something "peculiar" with the last Foreign Key
    FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT

    Look at ON DELETE RESTRICT
    Try it out in a local SQLite-Client
    Create tbl_teacher, tbl_subject and tbl_teacher_subject with my Constraints and then try to delete a teacher

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

  36. #76
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,287

    Re: Another complicated query but perhaps possible

    Btw, ON DELETE/UPDATE RESTRICT is default in most RDBMS so no need to be explicit about it. (In sqlite there are DEFERRABLE constraints so this is not exactly true for such FKs.)

    cheers,
    </wqw>

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

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by wqweto View Post
    Btw, ON DELETE/UPDATE RESTRICT is default in most RDBMS so no need to be explicit about it. (In sqlite there are DEFERRABLE constraints so this is not exactly true for such FKs.)

    cheers,
    </wqw>
    Not going to argue, but i rarely rely on "defaults" in such cases.
    If for whatever reason i have to look at the DDL-Statement, it's clear what the intention is
    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

  38. #78

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    Zvoni
    thank you very much
    I'm following your guidelines but I still can delete records in tbl_teacher_subject though they have reference in tbl_student_class.
    Of course I can't do that manually, but with code I can do.

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

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Zvoni
    thank you very much
    I'm following your guidelines but I still can delete records in tbl_teacher_subject though they have reference in tbl_student_class.
    Of course I can't do that manually, but with code I can do.
    Because in the "original" DDL for "tbl_student_class" i don't have the RESTRICT constraint
    Code:
    CREATE TABLE "tbl_student_class" (
        "ID"    INTEGER,
        "Student_ID"    INTEGER,
        "Class_ID"    INTEGER,
        "Price"    REAL DEFAULT NULL,
        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
    );
    FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,

    Change ON DELETE CASCADE there to ON DELETE RESTRICT, you won't be able to delete an entry from tbl_teacher_subject if you have an Entry in tbl_student_class
    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

  40. #80

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    930

    Re: Another complicated query but perhaps possible

    I dropped the tbl_student_class and created a new table using this code but
    I still can delete a record in tbl_teacher_subject that has an entry in tbl_student_class.
    Code:
    CREATE TABLE "tbl_student_class" (
        "ID"    INTEGER,
        "Student_ID"    INTEGER,
        "Class_ID"    INTEGER,
        "Price"    REAL DEFAULT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE RESTRICT,
        FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
    );
    I even add this code but in vain
    PRAGMA foreign_keys = ON

Page 2 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