# Thread: Another complicated query but perhaps possible

1. ## Re: Another complicated query but perhaps possible

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

this will only work, if you ignore teachers/students to receive a total sum
I think there is slight thing to modify in this code.
When I print RS!SumPayment, I get correct result, except if a subject is duplicated.
In this scenario:
Steven Spielberg's remaining = 0 (for Math)
James Cameron's remaining = 0 (for Physics)

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

this code gives correct output: 350

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

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

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

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

SELECT S.STName As Student,
COALESCE(SP.SumTotal, 0) AS SumTotal, COALESCE(SP.SumPayment, 0) AS SumPayment,
COALESCE(SP.SumTotal, 0)-COALESCE(SP.SumPayment, 0) As Remaining

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

2. ## Re: Another complicated query but perhaps possible

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

3. ## Re: Another complicated query but perhaps possible

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

4. ## Re: Another complicated query but perhaps possible

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

5. ## Re: Another complicated query but perhaps possible

I created this database
Code:
```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
End With
Cnn.CreateTable "tbl_student"
'
With Cnn.NewFieldDefs
End With
Cnn.CreateTable "tbl_subject"
'
With Cnn.NewFieldDefs
End With
Cnn.CreateTable "tbl_teacher"
''
With Cnn.NewFieldDefs
.Add "ID    INTEGER Primary Key "
.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 "Price    REAL DEFAULT NULL "
.Add "Myclass_id    REAL DEFAULT NULL "
" FOREIGN KEY(Class_ID) REFERENCES tbl_teacher_subject(ID) ON UPDATE CASCADE ON DELETE RESTRICT ," & _
End With
Cnn.CreateTable "tbl_student_class"

With Cnn.NewFieldDefs
.Add "ID   INTEGER PRIMARY KEY "
.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.
Code:
```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

6. ## Re: Another complicated query but perhaps possible

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

7. ## Re: Another complicated query but perhaps possible

You still didn't understand my requirement
Or what I'm looking for is not possible
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

8. ## Re: Another complicated query but perhaps possible

Originally Posted by newbie2
You still didn't understand my requirement
Or what I'm looking for is not possible
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)

9. ## Re: Another complicated query but perhaps possible

just leave out the WHERE…. =0
but I want the receipts of just those who have completed their payments.

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

10. ## Re: Another complicated query but perhaps possible

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

11. ## Re: Another complicated query but perhaps possible

Nice vaccation sir

#### Posting Permissions

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