Another complicated query but perhaps possible

[newbie2]

not possible.
how do you want to get the fully paid teachers, when you want the SUM across teachers?
same with students.

this will only work, if you ignore teachers/students to receive a total sum

I think there is slight thing to modify in this code.
When I print RS!SumPayment, I get correct result, except if a subject is duplicated.
In this scenario:
Steven Spielberg's remaining = 0 (for Math)
James Cameron's remaining = 0 (for Physics)

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

this code gives correct output: 350

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

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

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

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

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

[Zvoni]

You do realize that in your post you’re talking about teachers, but the query is for students?

[newbie2]

You do realize that in your post you’re talking about teachers, but the query is for students?

Sorry I confused teachers with students
So instead of Steven Spielberg,James Cameron and Stanley Kubrick, we can put
Adam Sandler; Elijah Wood and Nicole Kidman
But the idea is the same
thanks

[Zvoni]

OK, i did a Test with my Data, and added Adam Sandler to Physics with Steven Spielberg (duplicate Subject, but different teacher).
Returns correct Sum

Same with teacher.
So no idea what you're doing wrong

[newbie2]

I created this database

Dim StrSql As String
Dim DBFile 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"
  .Add "Date_In    ShortDate"
  .Add "Date_out    ShortDate  "
  .Add "sale_id    INTEGER"
  .Add "Agentid    INTEGER"
  .Add "Myclass_id    REAL DEFAULT NULL "
  .Add "school_year    INTEGER," & _
" 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"
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', '2', '25.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('3', '2', '3', '75.0')"
Cnn.Execute "INSERT INTO tbl_teacher_subject (ID, Teacher_ID, Subject_ID, TeacherPercentage) VALUES ('4', '3', '2', '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', 40,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('2', '1', '3', 25,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('3', '2', '5', 60,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('4', '3', '2', 40,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('5', '3', '4', 50,1)"
Cnn.Execute "INSERT INTO tbl_student_class (ID, Student_ID, Class_ID, Price,NumOfSessions) VALUES ('6', '2', '2', 30,1)"


Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('1', '20.0', '2023-01-10', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('2', '25.0', '2023-02-06', '2', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('3', '40.0', '2023-02-09', '4', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('4', '25.0', '2023-02-12', '2', 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', '20.0', '2023-02-28', '1', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('7', '40.0', '2023-01-24', '5', NULL);"
Cnn.Execute "INSERT INTO tbl_transaction (ID, Payment, PayDate, Student_Class_ID, Teacher_Subject_ID) VALUES ('8', '50.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', '20.0', '2023-03-15', NULL, '1');"

the sudents who have completed their payments are:
Adam Sandler (Physics) - payment = 40
Elijah Wood (Physics) - payment = 60
Nicole Kidman (Math) - - payment = 40
the total Sumpayment = 140

Then I used this query to get the total Sumpayment of the studentent who have completed their payments.

StrSql = "WITH " & _
   " TR  AS (SELECT SC.ID, SC.Student_ID, COALESCE(SC.Price, S.SubjectPrice) AS SubjectPrice, " & _
          "    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_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),  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 Student_ID ) " & _
"SELECT  " & _
"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 "
Set Rs = Cnn.OpenRecordset(StrSql)
 MsgBox Rs.RecordCount
MsgBox Rs!SumPayment

Rs.RecordCount = 0
I expect
Rs.RecordCount = 3
and
Rs!SumPayment = 140

[Zvoni]

Leave out the left join, remove tbl_student, and select directly FROM SP

i‘m currently out of office and preparing for a 10 day vacation.
no idea if i‘ll be able to look at it

EDIT: Found it.

Problem: In Post 110 (or was it 109?) you said ..." paid in full IRRESPECTIVE of subject" It doesn't matter if the subject itself is paid or not, just if the STudent has paid all he has to pay
Your "Error" has nothing to do if a Subject exist twice

I added TR.ID in SP incl. the Group By

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.ID, TR.Student_ID, SUM(TR.TotalSubjectPrice) As SumTotal, SUM(TR.SumPayment) As SumPayment 
            FROM TR GROUP BY TR.Student_ID, TR.ID)
            
SELECT S.STName As Student, 
COALESCE(SP.SumTotal, 0) AS SumTotal, COALESCE(SP.SumPayment, 0) AS SumPayment, 
COALESCE(SP.SumTotal, 0)-COALESCE(SP.SumPayment, 0) As Remaining

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

That said: No idea what this information is worth to you.
You'd see that Adam Sandler has to pay 40, he has paid 40, but you don't see, that Adam Sandler has overpaid english

[newbie2]

You still didn't understand my requirement
Or what I'm looking for is not possible
Please see my last post
I want to display The total receipts of the three students all together:
Adam Sandler, Elijah Wood and Nicole Kidman
the total receipt is 140
When I click on a buttom, a message pops up with: 140
Is this possible or not?
thank you

[Zvoni]

You still didn't understand my requirement
Or what I'm looking for is not possible
Please see my last post
I want to display The total receipts of the three students all together:
Adam Sandler, Elijah Wood and Nicole Kidman
the total receipt is 140
When I click on a buttom, a message pops up with: 140
Is this possible or not?
thank you

if you want to see totalpayment just leave out the WHERE…. =0 of your query (not mine)

[newbie2]

just leave out the WHERE…. =0

but I want the receipts of just those who have completed their payments.

StrSql = "WITH " & _
   " TR  AS (SELECT SC.ID, SC.Student_ID, COALESCE(SC.Price, S.SubjectPrice) AS SubjectPrice, " & _
          "    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_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),  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 Student_ID ) " & _
"SELECT  " & _
"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 " 
Set Rs = Cnn.OpenRecordset(StrSql)
 MsgBox Rs.RecordCount
MsgBox Rs!SumPayment

Still Rs.RecordCount = 0

[Zvoni]

And that’s the problem: there is none, since you sum up everything
this is not making any sense whatsoever

that said: i‘m on vacation the next 10 days

[newbie2]

Nice vaccation sir