Another complicated query but perhaps possible

[newbie2]

Hello evryone
First of all I have to thank jdelano and Zvoni who have helped me in my last thread.
In fact everything is OK except I was met with a case wher a teacher is assigned two different subjects.
Here is the case:

Set Cnn = New_c.Connection(, DBCreateInMemory)
With Cnn.NewFieldDefs
 .Add "St_ID   Integer "
 .Add "T_ID   Integer "
 .Add "Sfees   Double"
 .Add "subj_ID   Integer"
End With
Cnn.CreateTable " Tbl1"
Cnn.Execute "INSERT INTO Tbl1 (St_ID, T_ID, Sfees, subj_ID) VALUES (1,1,40, 1)"
Cnn.Execute "INSERT INTO Tbl1 (St_ID, T_ID, Sfees, subj_ID) VALUES (2,1,30, 1)"
Cnn.Execute "INSERT INTO Tbl1 (St_ID, T_ID, Sfees, subj_ID) VALUES (3,1,40, 2)"

With Cnn.NewFieldDefs
.Add "T_ID   Integer "
.Add "N_Trans   Integer"
.Add "TFees   Double"
End With
Cnn.CreateTable " Tbl2"
Cnn.Execute "INSERT INTO Tbl2 ( T_ID,  N_Trans, TFees) VALUES ( 1, 1, 10)"
Cnn.Execute "INSERT INTO Tbl2 ( T_ID , N_Trans, TFees) VALUES ( 1, 1, 10)"
Cnn.Execute "INSERT INTO Tbl2 ( T_ID , N_Trans, TFees) VALUES ( 1, 2, 10)"

With Cnn.NewFieldDefs
 .Add "T_ID   Integer "
 .Add "N_Trans   Integer"
 .Add "subj_ID   Integer"
End With
Cnn.CreateTable "Tbl3"
Cnn.Execute "INSERT INTO Tbl3 ( T_ID,  N_Trans, subj_ID) VALUES ( 1, 1, 1)"
Cnn.Execute "INSERT INTO Tbl3 ( T_ID , N_Trans, subj_ID) VALUES ( 1, 2, 2)"

With Cnn.NewFieldDefs
 .Add "ID   Integer Primary key"
 .Add "Subj   Text"
 End With
Cnn.CreateTable "Subj_tbl"

Cnn.Execute "INSERT INTO Subj_tbl (ID, Subj) VALUES ('1', 'Phys')"
Cnn.Execute "INSERT INTO Subj_tbl (ID, Subj) VALUES ('2', 'Maths')"

StrSql = "StrSql = "SELECT T2.N_Trans, Subj, T1.Sfees * 50 / 100 as Totalprice , T3.N_Trans, T2.TFees, Subj_tbl.ID, T2.T_ID, (T1.Totalprice *  50  /100)-SUM(T2.TFees) OVER(PARTITION BY T2.N_Trans,T3.subj_ID " & _
" ORDER BY T2.ROWID) As RestToPay " & _
" FROM (SELECT subj_ID, T_ID, subj_ID, Sfees, SUM(Sfees) AS Totalprice FROM Tbl1 Where T_ID = 1   GROUP BY T_ID,subj_ID ) AS T1 " & _
"left join Subj_tbl on T1.subj_ID = Subj_tbl.ID " & _
" INNER JOIN Tbl2 AS T2 ON T1.T_ID=T2.T_ID " & _
" INNER JOIN Tbl3 AS T3 ON T2.N_Trans=T3.N_Trans and T1.subj_ID=T3.subj_ID Where T2.T_ID = 1 GROUP BY T3.T_ID,T1.subj_ID, T3.N_Trans "
Set Rs = Cnn.OpenRecordset(StrSql)

I have been struggling for long hours to produce this output but no succes.

Subj	TotalPrice	Payments	Remaining
Phys	35	10	25
Phys	35	10	15
Maths	20	10	10

(30 +40) * 50/100 = 35
40 *50/100 = 20
2 payments were made for phys (10 + 10)
1 payment was made for Maths (10)

[Zvoni]

This is one convoluted setup i‘ve rarely seen.

my way:

teacher is in a m:m relation with subjects —> this is a „class“. A teacher can have multiple subjects, a subject can have multiple teachers. The fee goes into the m:m „class“ table
“what’s the total price for this subject taught by that teacher“
optional: the fee could go into the subject table, if it describes the Price for the subject itself, independent who is teaching it

student is in a m:m relation with „class“. A student can be in multiple classes, a class can have multiple students

Student is in a 1:m relation with transactions. A student pays multiple fees
„student_class“ is in a 1:m relation with transactions. A student _class can receive multiple transactions
transactions needs 2 foreign keys, identifying which student has paid for which class

will have to think on it

[techgnome]

Might help if you gave your tables some meaningful names ... "tbl1" doesn't tell anyone what's in it ... call it what it is... and I don't know what that is so... subj_table is marginally better... "subject" would be best...
But...

If yolu have teachers that teach a suvject, then the subject id should be part of the teacher table... how ever, since you can have a teacher with multoiple subjects, you should have a "subject" table, a "teacher" table, and a "teacher_subject" table. The subject table is a table of subjects. and the teacher is the list of teachers ... teacher_subject then links the two in a many-to-many relationship. A teacher can have multiple subjects, and a subject can have multiple teachers.

-tg

[Zvoni]

Tg,
do you see the same thing as me in his table1?
student id=2 has a different total price to pay than student id=1 for the same subject with the same teacher?

crap. That would mean „individual“ pricing
would mean in my setup above, the fee would go into the student-class connector table, right?

[techgnome]

I see it now... I kinda glossed over because I couldn't tell what tables were what ... but yeah... I see that... and that....bleh... is not good. This is why tables and fields should have reasonable names. "Subject" "Student" "Teacher" "Student_class" "Payments" ... and so on...


-tg

[newbie2]

Zvoni and techgnome
thank you for your precious remarks
Indeed my tables in my database have meaningful names.
But because these are temporary tables I have given temporary names , I have even dropped some fields and joins.
I just kept what I thought necessary.
I consider this as negligence from my part.

student id=2 has a different total price to pay than student id=1 for the same subject with the same teacher?

I'm sorry I was not so careful.
Students pay the same price for the same subject.

Well in my last thread Zvoni and jdelano suggested good codes. Both are working but the codes deal with one case when a teacher has one subject.
Now I'm faced with a situation where a teacher has two subjects.
I tried to modify the codes but I failed.

Dim SQL As String
SQL = "SELECT P1.p1sum,L1.Sfees, P1.T_ID, P1.p1sum -L1.Sfees as res_sum " & _
" FROM (SELECT T_ID, SUM(TFees) as p1sum FROM Tbl1 P1 GROUP BY P1.T_ID) P1 " & _
" INNER JOIN (SELECT T_ID, Sfees FROM Tbl2 L1 ) L1 " & _
" ON P1.T_ID= L1.T_ID " & _
" ORDER BY P1.T_ID DESC"

recCount = 1
runningRemaining = 0

MSFG.TextMatrix(0, 0) = "TFeeSum"
MSFG.TextMatrix(0, 1) = "SFee"
MSFG.TextMatrix(0, 2) = "Remaining"

Do While Not rs.EOF
If recCount = 1 Then
TFeesSum = rs(0) ' grab the TFeesSum from the first record
runningRemaining = rs(0)
End If

MSFG.TextMatrix(recCount, 0) = TFeesSum
MSFG.TextMatrix(recCount, 1) = rs(1)

runningRemaining = runningRemaining - rs(1)
MSFG.TextMatrix(recCount, 2) = runningRemaining
rs.MoveNext

recCount = recCount + 1
Loop


[/CODE]

[QUOTE=Zvoni;5631686]Let's get this one sorted out first

SELECT T1.p1sum, T2.SFees, T1.p1sum-SUM(T2.SFees) OVER(PARTITION BY T2.T_ID ORDER BY T2.ROWID) As res_sum
FROM (SELECT T_ID, SUM(TFees) AS p1sum FROM Tbl1 GROUP BY T_ID) AS T1     
INNER JOIN Tbl2 AS T2 ON T1.T_ID=T2.T_ID

returns:

p1sum	SFees	res_sum
30.0	4.0	26.0
30.0	2.0	24.0
30.0	3.0	21.0

[Zvoni]

Right.
Here is a Proof of Concept for my idea.

Newbie2, as tg stated: Give meaningful names to your samples, and give plenty of sample records.

Here is the fiddle for everyone, who doesn't want to execute the DDL's or doesn't have SQLite:
https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/0

Here's the Code to create and populate everything

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

CREATE TABLE "tbl_subject" (
    "ID"    INTEGER,
    "SName"    TEXT,
    "SubjectPrice"    REAL DEFAULT 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,
    "ClassPrice"    REAL DEFAULT NULL,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);

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

CREATE TABLE "tbl_transaction" (
    "ID"    INTEGER,
    "Payment"    REAL,
    "PayDate"    TEXT,
    "Student_Class_ID"    INTEGER,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);

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

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

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

INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('1', '1', '1', '80.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('2', '1', '3', '70.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('3', '2', '2', '60.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('4', '3', '3', '65.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('5', '2', '1', '100.0');

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

INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('1', '10.0', '2023-01-10', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('2', '20.0', '2023-02-06', '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('3', '30.0', '2023-02-09', '4');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('4', '10.0', '2023-02-12', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('5', '15.0', '2023-02-25', '3');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('6', '10.0', '2023-02-28', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('7', '60.0', '2023-01-24', '5');

Here's the SQL for the Query:

SELECT 
ST.STName As Student, S.SName As Subject, T.TName As Teacher, 
COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice) As TotalPrice, 
COALESCE(TR.Payment, 0) As Payment, TR.PayDate,
COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice)-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As Remaining
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	Teacher	TotalPrice	Payment	PayDate	Remaining
Adam Sandler	Physics	James Cameron	30.0	10.0	2023-01-10	20.0
Adam Sandler	Physics	James Cameron	30.0	10.0	2023-02-12	10.0
Adam Sandler	Physics	James Cameron	30.0	10.0	2023-02-28	0.0
Adam Sandler	Math	Steven Spielberg	40.0	20.0	2023-02-06	20.0
Adam Sandler	English	Stanley Kubrick	65.0	60.0	2023-01-24	5.0
Elijah Wood	Physics	Steven Spielberg	100.0	15.0	2023-02-25	85.0
Elijah Wood	English	James Cameron	70.0	0		70.0
Nicole Kidman	English	James Cameron	20.0	30.0	2023-02-09	-10.0

How it works:
A Subject has a "Price" (Say, determined by the School)
A Teacher has a Price for a Subject, which can differ from the "general" Price for a Subject
A Student can negotiate an individual Price for a Subject with a specific teacher.

The COALESCE on the Prices works as follows: If a Student has an individual Price, return that one, if No, does the teacher has his "own" Price for a Subject, If no, return the "general" Price for the Subject

EDIT: Just noticed: The COALESCE for the Prices needs a fourth Parameter "0" in case all 3 Prices are NULL.
In that case that "Class" would be "free of Charge"
COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice, 0) As TotalPrice....
....
COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice, 0)-SUM(COALESCE(....

[newbie2]

Zvoni
You are so generous.
Thank you very much for sharng this valuable work.
I'm now travelling and I 'll test this once at home.
Just small remarks:
A teacher payment is not static. It s calculted per number of sessions he will teach.
A subject has not th same price for teachers. A well-experienced teacher is not paid the same price as a less experienced one for the same subject.
The teacher's payments may be done in installments.
I don't know whether the code takes this into consederation.
thank you.

[Zvoni]

Zvoni
A teacher payment is not static. It s calculted per number of sessions he will teach.
A subject has not th same price for teachers. A well-experienced teacher is not paid the same price as a less experienced one for the same subject.
The teacher's payments may be done in installments.
I don't know whether the code takes this into consederation.
thank you.

See my post

A Subject has a "Price" (Say, determined by the School)
A Teacher has a Price for a Subject, which can differ from the "general" Price for a Subject
A Student can negotiate an individual Price for a Subject with a specific teacher.

[newbie2]

Zvoni
I 'm still away from home and I haven't tested the demo yet. Perhaps I'll do tomorrow .
I have a slight worry :
Do I need to have another transaction table for the teachers payments?
Of course a teacher accumulates his pay from the students fees for the subject he teaches.
Also The teacher's payments may be done in installments.
thank you very much

[Zvoni]

Zvoni
I 'm still away from home and I haven't tested the demo yet. Perhaps I'll do tomorrow .
I have a slight worry :
Do I need to have another transaction table for the teachers payments?
Of course a teacher accumulates his pay from the students fees for the subject he teaches.
Also The teacher's payments may be done in installments.
thank you very much

No, it's exactly as i've written above: You only have one transaction-table and that one connects to the "Student-Class" Table.
In other words: The student is not paying the Teacher per se, he is paying the "class", which is a combination of a specific subject and a specific teacher.

If you have the requirement, e.g. James Cameron is teaching "Math" and he demands 100USD, independant, how many students he has in that class, then we have to expand the query with a Count of Students, but that's not black magic, either.
Say, James Cameron in Math wants 100USD, and he has 5 Students, so each student would need to pay 20USD in Installments of 5USD is pretty easy to expand

[newbie2]

Mr Zvoni
Please excuse my ignorance
Sorry i'm still away from home and I'm writing from my mobile
Imagine a teacher has accumulated $100
The school pays him only $50 for the current week. The remaining is $50
Where am I going to store these installments?
Thank you very much

[Zvoni]

Mr Zvoni
Please excuse my ignorance
Sorry i'm still away from home and I'm writing from my mobile
Imagine a teacher has accumulated $100
The school pays him only $50 for the current week. The remaining is $50
Where am I going to store these installments?
Thank you very much

I don't understand this.
Who is paying the Teacher? The School or the Students?
Because in your inquiries and code-samples the students pay the teachers fee, not the school
So where do the 100USD/50USD now come from?

[wqweto]

I really hope this is not a prodcution system but just some sample code as an excercise. . . This is totally messed up already on conceptual level :-))

cheers,
</wqw>

[newbie2]

I don't understand this.
Who is paying the Teacher? The School or the Students?
Because in your inquiries and code-samples the students pay the teachers fee, not the school
So where do the 100USD/50USD now come from?

The students pay the school and the school pays the teachers.
A teacher may take 50% of his students's fees another one may take 60% and so on.
The school may give the teacher the whole wage as they can pay him in installments. I mean several payments.

[Zvoni]

The students pay the school and the school pays the teachers.
A teacher may take 50% of his students's fees another one may take 60% and so on.
The school may give the teacher the whole wage as they can pay him in installments. I mean several payments.

Ahhh.... OK...let me think on it, but i don't really see any serious problems

Just to see, if i understood everything correctly:

The School says "English-Class costs 100 $" for all Students
The School has 2 teachers for English, The first teacher demands 50% of the Fee (leaving 50% for the School), the other teacher 75% (leaving 25% for the School)

The School says "Math costs 200 $" for all Students
The School has a teacher, who teaches both, Math and English
The teacher demands for English 50%, for Math 60%

That correct?

EDIT: I see two ways to skin that cat
1) as a percentage ("50% of 100 $") --> That one would involve multiplications on the fly within the SQL-Query
2) as an absolute ("50 $ of those 100 $") --> That one would involve a simple subtraction

If you want to see the "payable" Transactions from School to teacher, then yes, you need a second Transaction-Table (which IMO would be the proper way)

[newbie2]

Many many thanks for understanding me
that's what I'm wishing to do

[newbie2]

Would you please sir help me with building the teacher_transaction table and the foreign keys?

[Zvoni]

Slightly changed fiddle:
https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/3

DDL

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

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

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

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

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

CREATE TABLE "tbl_transaction" (
    "ID"    INTEGER,
    "Payment"    REAL,
    "PayDate"    TEXT,
    "Student_Class_ID"    INTEGER,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);

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

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

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

INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('1', '1', '1', '80.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('2', '1', '3', '70.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('3', '2', '2', '60.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('4', '3', '3', '65.0');
INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('5', '2', '1', '100.0');

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

INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('1', '10.0', '2023-01-10', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('2', '20.0', '2023-02-06', '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('3', '30.0', '2023-02-09', '4');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('4', '10.0', '2023-02-12', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('5', '15.0', '2023-02-25', '3');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('6', '10.0', '2023-02-28', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('7', '60.0', '2023-01-24', '5');

SQL-Query

SELECT ST.STName As Student, S.SName As Subject, S.SubjectPrice As SubjectPrice, 
COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice,
TR.PayDate,
T.TName As Teacher, 
TS.ClassPrice As TeacherPrice,
COALESCE(TS.ClassPrice/S.SubjectPrice*TR.Payment, 0) As PaymentBySchoolToTeacher,
--100*TS.ClassPrice/S.SubjectPrice As RatioPercent,
TS.ClassPrice-SUM(COALESCE(TS.ClassPrice/S.SubjectPrice*TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingTeacherPrice, 
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	PaymentByStudentToSchool	RemainingSubjectPrice	PayDate	Teacher	TeacherPrice	PaymentBySchoolToTeacher	RemainingTeacherPrice	PayDate
Adam Sandler	Physics	100.0	10.0	90.0	2023-01-10	James Cameron	80.0	8.0	72.0	2023-01-10
Adam Sandler	Physics	100.0	10.0	80.0	2023-02-12	James Cameron	80.0	8.0	64.0	2023-02-12
Adam Sandler	Physics	100.0	10.0	70.0	2023-02-28	James Cameron	80.0	8.0	56.0	2023-02-28
Adam Sandler	Math	200.0	20.0	180.0	2023-02-06	Steven Spielberg	60.0	6.0	54.0	2023-02-06
Adam Sandler	English	300.0	60.0	240.0	2023-01-24	Stanley Kubrick	65.0	13.0	52.0	2023-01-24
Elijah Wood	Physics	100.0	15.0	85.0	2023-02-25	Steven Spielberg	100.0	15.0	85.0	2023-02-25
Elijah Wood	English	300.0	0	300.0		James Cameron	70.0	0	70.0
Nicole Kidman	English	300.0	30.0	270.0	2023-02-09	James Cameron	70.0	7.0	63.0	2023-02-09

Don't forget the Sanity-Checks (Teacher's fee cannot be higher than the Fee the School demands, etc.)

Note: I've left the option for "individual" Prices for individual Students. Here the Sanity-Check should be:
Individual Price is BETWEEN SchoolPrice ("SubjectPrice") and TeachersPrice -->
Individual Price can be lower than School's Price for Subject, but must be higher than teacher's price

Note2: This is WITHOUT a second Transaction-Table "SchoolToTeacher" --> It implies that any Payment from Student to School gets forwarded from School to Teacher (adjusted by "ratio") IMMEDIATELY!
If the Payment SchoolToTeacher is NOT synchronized with payment StudentToSchool, THEN you need a second transaction-table

[newbie2]

you are so generous sir
All words of the word fail to thank you enough
I've just come home. I'm so tired.
Tomorrow morning I'll proceed with the new conception
thank you million times.

[newbie2]

Hello Zvoni
Sorry to disturb you again
I'm having some problems.
1st problem in calculating students' fees.
students' fees are calculated by muliplying the price of the subject with the number of sessions the student has atteded.
For this, I added a column in tbl_student_class called NumOfSession.

StrSql = "SELECT TR.PayDate, TR.ID, SC.Price, TR.Payment  ," & _
" ST.STName As Student, S.SName As Subject, T.TName As Teacher," & _
" COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice) * Num_sessions As TotalPrice," & _
" COALESCE(TR.Payment, 0)  As Payment, " & _
" COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice)- SUM(COALESCE(TR.Payment, 0)* Num_sessions )" & _
" OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As Remaining " & _
" 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 " & _
" Where St.ID = " & NT(Text5.Text, 0) & " and " & _
" TS.Subject_ID = " & NT(Text6.Text, 0) & " and " & _
" SC.Class_ID = " & NT(Text8.Text, 0) & " " & _
" ORDER BY St.ID, SC.ID, TR.PayDate"
Set Rs = Cnn.OpenRecordset(StrSql)

the result is this:

Subject	Price	payments	remaing
Physics	200	10	90
Physics	200	15	75

Where it should be:

Subject	Price	payments	remaing
Physics	200	10	190
Physics	200	15	175

[Zvoni]

You have to insert your numsession factor in line 5 of your query to the first operand.
look closely at what’s left of sum over (left of the minus): it‘s the same operand as in line 3.
you‘re applying your factor to the payment. Wrong.
the factor only applies to totalprice, as in line 3

and qualify your num_session column with the correct alias SC

[newbie2]

The second issue is related to calculating the teachers ' wages.

The teachers ' wages are calculated by the sums of students fees stored in tbl_transaction
but I need to subtract the school part and also subtract any other previous payment stored in Teacher_transaction_tbl.
For this I added a new Teacher_transaction_tbl and a new column in tbl_teacher_subject called (teacher_percentage).
To calculate a teachers wage.

teachers wage = sum (sts_fees) * teacher_percentage /100 - sum (payments already made for a specific subject )
Example:
James Cameron has accumulated 100 USD for physics..
His percentage for this subject in teacher_percentage = 60 (60%)
He has already received 40 USD
To calculate the new wage is: 100 *60/100 - 40 = 20

An other scenario is that
James Cameron has accumulated 200 USD for English.
He hasn't received any payments yet.
His percentage in teacher_percentage = 50 (50%)
He has no reference in Teacher_transaction_tbl with this subject.
To calculate his wage is: 200 *50/100 = 100
Here I'm stuck.
thank you a lot

[Zvoni]

200*50/100 is 100 not 50

will be Monday when i can have a look

[newbie2]

200*50/100 is 100 not 50

will be Monday when i can have a look

You are right
take your time sir

[newbie2]

You have to insert your numsession factor in line 5 of your query to the first operand.
look closely at what’s left of sum over (left of the minus): it‘s the same operand as in line 3.
you‘re applying your factor to the payment. Wrong.
the factor only applies to totalprice, as in line 3

and qualify your num_session column with the correct alias SC

Thank you very much

[Zvoni]

To get that first thing (TeacherPercentage and NumOfSessions) out of the Way.

Changed fiddle.
https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/4

Some changed and added columns
DDL

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

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

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

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

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

CREATE TABLE "tbl_transaction" (
    "ID"    INTEGER,
    "Payment"    REAL,
    "PayDate"    TEXT,
    "Student_Class_ID"    INTEGER,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
);

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

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

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

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

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

INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('1', '10.0', '2023-01-10', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('2', '20.0', '2023-02-06', '2');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('3', '30.0', '2023-02-09', '4');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('4', '10.0', '2023-02-12', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('5', '15.0', '2023-02-25', '3');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('6', '10.0', '2023-02-28', '1');
INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('7', '60.0', '2023-01-24', '5');

SQL

SELECT ST.STName As Student, S.SName As Subject, S.SubjectPrice As SubjectPrice, 
SC.NumOfSessions,
S.SubjectPrice*SC.NumOfSessions As SubjectTotal,
COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice,
TR.PayDate,
T.TName As Teacher, 
TS.TeacherPercentage As TeacherPercentage,
TS.TeacherPercentage/100*S.SubjectPrice*SC.NumOfSessions AS TeacherTotal,
COALESCE(TS.TeacherPercentage/100*TR.Payment, 0) As PaymentBySchoolToTeacher,
TS.TeacherPercentage/100*SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TS.TeacherPercentage/100*TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingTeacherPrice, 
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	NumOfSessions	SubjectTotal	PaymentByStudentToSchool	RemainingSubjectPrice	PayDate	Teacher	TeacherPercentage	TeacherTotal	PaymentBySchoolToTeacher	RemainingTeacherPrice	PayDate
Adam Sandler	Physics	100.0	1	100.0	10.0	90.0	2023-01-10	James Cameron	50.0	50.0	5.0	45.0	2023-01-10
Adam Sandler	Physics	100.0	1	100.0	10.0	80.0	2023-02-12	James Cameron	50.0	50.0	5.0	40.0	2023-02-12
Adam Sandler	Physics	100.0	1	100.0	10.0	70.0	2023-02-28	James Cameron	50.0	50.0	5.0	35.0	2023-02-28
Adam Sandler	Math	200.0	2	400.0	20.0	380.0	2023-02-06	Steven Spielberg	75.0	300.0	15.0	285.0	2023-02-06
Adam Sandler	English	300.0	1	300.0	60.0	240.0	2023-01-24	Stanley Kubrick	50.0	150.0	30.0	120.0	2023-01-24
Elijah Wood	Physics	100.0	1	100.0	15.0	85.0	2023-02-25	Steven Spielberg	25.0	25.0	3.75	21.25	2023-02-25
Elijah Wood	English	300.0	2	600.0	0	600.0		James Cameron	25.0	150.0	0	150.0
Nicole Kidman	English	300.0	3	900.0	30.0	870.0	2023-02-09	James Cameron	25.0	225.0	7.5	217.5	2023-02-09

Now the pertinent question: Is the teacher payed "asynchronously" to the Students payments?
If yes, then you need "separate" transactions to the students transactions.
I have an idea to solve that with the already existing transaction-table.
Will have to test for feasabilty

[Zvoni]

OK, did some tests.

If your teacher's payment is "asynchronous" it's not going to be possible to show that in one single query, because, in a nutshell, it's uncorrelated Data

e.g. English with James Cameron
he has 2 Students. E. Wood with 600$ total (2 sessions at 300$), and N.Kidman with 900$ total (3 sessions at 300$), making it 1500$ Total overall, with 25% of it going to the Teacher, making it 375$ (25% of 1500$)

Elijah Wood has not paid anything for his 2 sessions right now
Nicole Kidman has paid one installment for her three sessions on 2023-02-09, leaving her at 870$ remaining total

Now imagine Elijah Wood paying his first installment of 100$ on 2023-02-15. He'd have a remaining total of 500$

If the School now pays James Cameron "accumulated" 32,50$ (25% of 100$ and 25% of 30$) on say 2023-02-20, how would you correlate Data?
This only works in separate queries, and has nothing to do with if it's two transaction tables or one (and Yes, my idea does work with one transaction-table)

btw: Is "NumOfSessions" individual per Student ("E.Wood needs 2 Sessions, N.Kidman needs 3 Sessions"), or is it per "Class" ("English is taught in 3 Sessions for all Students")?

[newbie2]

Billions thanks sir
You are really great man.
I have tested the code as a whole and it worked like a charm.
I need some time to split the code and apply it to specific situations.

Now the pertinent question: Is the teacher payed "asynchronously" to the Students payments?

I do not know whether i have understood your question well or not but I think the answer is yes as described in post 23.

I have an idea to solve that with the already existing transaction-table.
Will have to test for feasabilty

That will be something of magic.

Million thanks again

[newbie2]

I'm sorry I have just seen your last post.

btw: Is "NumOfSessions" individual per Student ("E.Wood needs 2 Sessions, N.Kidman needs 3 Sessions"),
or is it per "Class" ("English is taught in 3 Sessions for all Students")?

It is individual.
one student may study 6 sessions for English but another one may enroll for 10 for the same subject.

[newbie2]

I think I start to get my happiness.
I created a new table "Transaction_teacher_tbl"

CREATE TABLE "Transaction_teacher_tbl" (
    "ID"    INTEGER,
    "Teacher_ID"    INTEGER,
    "Payment"    REAL,
    "PayDate"    DateTime,
    "Subj_ID"    INTEGER ,
    PRIMARY KEY("ID"),
    FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("Subj_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE

INSERT INTO "Transaction_teacher_tbl" ("ID", "Teacher_ID", "Payment", "PayDate","Subj_ID" ) VALUES ('1', '1', '20', '2023-01-10',1);
INSERT INTO "Transaction_teacher_tbl" ("ID", "Teacher_ID", "Payment", "PayDate", "Subj_ID") VALUES ('2', '1', '15', '2023-01-11',1);
INSERT INTO "Transaction_teacher_tbl" ("ID", "Teacher_ID", "Payment", "PayDate", "Subj_ID") VALUES ('3', '1', '', '2023-01-12',3);

StrSql = "SELECT  S.SName As Subject,  " & _
" T.TName As Teacher, " & _
" TS.TeacherPercentage As TeacherPercentage, " & _
" TS.TeacherPercentage/100*S.SubjectPrice*SC.NumOfSessions AS TeacherTotal, " & _
" COALESCE(TR.Payment, 0) As PaymentBySchoolToTeacher," & _
" TS.TeacherPercentage/100*SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingTeacherPrice, " & _
" TR.PayDate " & _
" FROM tbl_teacher AS T " & _
" INNER JOIN tbl_teacher_subject AS TS ON TS.Teacher_ID=T.ID " & _
" INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
" INNER JOIN tbl_student_class AS SC ON SC.Class_ID=S.ID " & _
"  LEFT JOIN Transaction_teacher_tbl AS TR ON TR.Subj_ID=TS.Subject_ID and TR.Teacher_ID=TS.Teacher_ID " & _
" ORDER BY T.ID, TS.ID, TR.PayDate"
Set Rs = Cnn.OpenRecordset(StrSql)

I'm getting this output which is what I have been struggling for days to get it.
However I still need more time to deal with similar transactions.
Attachment 190456

[Zvoni]

Right, there is something weird going on with the Forum.
Can't post the DDL for a specific Constraint?!?!?!

Anyway,
my Solution with only one Transaction-table
Let's see if anyone can spot the "magic"
DDL

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

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

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

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

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

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

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

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

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

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

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

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

Query to return Students payments

SELECT ST.STName As Student, S.SName As Subject, S.SubjectPrice As SubjectPrice, T.TName As TeacherName,
SC.NumOfSessions,
S.SubjectPrice*SC.NumOfSessions As SubjectTotal,
COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
SC.NumOfSessions*S.SubjectPrice-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	100.0	James Cameron	1	100.0	10.0	90.0	2023-01-10
Adam Sandler	Physics	100.0	James Cameron	1	100.0	10.0	80.0	2023-02-12
Adam Sandler	Physics	100.0	James Cameron	1	100.0	10.0	70.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

Query to return teacher's Payment

WITH 
    SC    AS (SELECT Class_ID, SUM(NumOfSessions) AS NumOfSessions FROM tbl_student_class
            GROUP BY Class_ID)

SELECT 
S.SName As SubjectName, S.SubjectPrice, T.TName As TeacherName, TS.TeacherPercentage, 
S.SubjectPrice*TS.TeacherPercentage/100 As TeacherPrice,
COALESCE(SC.NumOfSessions, 0) As TotalSessions,
S.SubjectPrice*TS.TeacherPercentage/100*COALESCE(SC.NumOfSessions, 0) As TotalTeacherPrice,
COALESCE(TR.Payment,0) AS Payment,
S.SubjectPrice*TS.TeacherPercentage/100*COALESCE(SC.NumOfSessions, 0)-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

Returns

SubjectName	SubjectPrice	TeacherName	Teacher Percentage	TeacherPrice	TotalSessions	Total TeacherPrice	Payment	TotalTeacher PriceRemaining	PayDate
Physics	100.0	James Cameron	50.0	50.0	3	150.0	30.0	120.0	2023-03-05
Physics	100.0	James Cameron	50.0	50.0	3	150.0	25.0	95.0	2023-03-15
English	300.0	James Cameron	25.0	75.0	5	375.0	30.0	345.0	2023-02-15
Math	200.0	Steven Spielberg	75.0	150.0	2	300.0	0	300.0
English	300.0	Stanley Kubrick	50.0	150.0	1	150.0	0	150.0
Physics	100.0	Steven Spielberg	25.0	25.0	1	25.0	0	25.0

[Steve R Jones]

test - Posting works for me.

Post #27 - I recommend you remove that if possible...

[Zvoni]

Post #27 - I recommend you remove that if possible...

Because of the "widespread" of the Forum? Yeah, thought about it.

But i (seem to?) have figured out what the problem is/was.
See my post in Forum-Feedback

[newbie2]

Zvoni
Thank you very much the magical work you are doing.

Let's see if anyone can spot the "magic"

Frankly I am not up to the task to do that.
Zvoni
I see that the teacher is payed synchronously to the Students payments which does not fit my situation.
Thus, I think a Teacher_Transaction table is necessary. as I did in post 31.
thanks

[Zvoni]

Zvoni
I see that the teacher is payed synchronously to the Students payments which does not fit my situation.
Thus, I think a Teacher_Transaction table is necessary. as I did in post 31.
thanks

No, he's not.
Look closely at the "Paydate" in my last example (Post 32)
Compare the Paydate of Student-Payments to the Paydate of Teacher's Payment

EDIT: The teacher's Payment has nothing to do with Student's Payment.
It's up to the School HOW much they transfer to the teacher's account

[newbie2]

EDIT: The teacher's Payment has nothing to do with Student's Payment.
It's up to the School HOW much they transfer to the teacher's account

Looking closely to the code, I figured out that students payments and teachers payments are done seperately in the same table.
Mr Zvoni:
A question out of curiosity:
Which method is simpler but also performant for a newbie:
Having one transaction table to track both payment?
or having two seperate transaction tables?
thank you

[Zvoni]

Looking closely to the code, I figured out that students payments and teachers payments are done seperately in the same table.
Mr Zvoni:
A question out of curiosity:
Which method is simpler but also performant for a newbie:
Having one transaction table to track both payment?
or having two seperate transaction tables?
thank you

For a newbie? two tables. Easier to understand the DDL-Statements

For someone advanced? One table, with a "specialty" i haven't mentioned above:
in your scenario i'd store teacher's payments as negative values ("James Cameron was paid -30$")

Reason:
Imagine you want to calculate the "net"-result of the School.
That means to sum up all Student-Payments, but then separately subtract teacher payments
With two tables you have to use JOINS, with one table, you just add up (see my specialty above)

The other reason for one table (which is actually IMO the main one):
You have to think of that transaction-table like a "bank-account"

Take a look at your own Bank-Account: You have payments coming in (salary etc.), you have payments going out (rent for Appartment etc.), but both happen in one "table" (=Account)

Remember: A Database should "reflect" the "Environment" (A school) it was designed for "as close as possible to real life"

Performance-wise? No idea

EDIT: I have to amend the specialty above.
I'd introduce an additional Field "TransactionSign" or similar in the transaction-table, which only accepts two values: 1 and -1
The payment itself would still get stored as a positive value, but depending if it's a Students payment it would get Sign=1, and if it's a Payment to a teacher it would get Sign=-1
So it would be a simple multiplication, and the "Sign"-Field is never shown anywhere in any statistics.
I'd even add that Sign-Column as a Generated Column with Attribute STORED, though VIRTUAL should work the same

[newbie2]

Zvoni
Excuse my weak understanging.
I think I see another issue .
I noticed that the teacher's price is retrieved from the tbl_subject (subject_price) however in my situation the teacher's price is accumulated by summing the students' payments.
For example the price calculted for James Cameron = sum of students payments(for physics) * TeacherPercentage/100*NumOfSessions.
I don't know whether I understand the code well or not.
thank you

[Zvoni]

Zvoni
Excuse my weak understanging.
I think I see another issue .
I noticed that the teacher's price is retrieved from the tbl_subject (subject_price) however in my situation the teacher's price is accumulated by summing the students' payments.
For example the price calculted for James Cameron = sum of students payments(for physics) * TeacherPercentage/100*SC.NumOfSessions.
I don't know whether I understand the code well or not.
thank you

OK, i'll try to explain:
Let's take a single Subject (Physics), which the school says: "One session costs 100$"
Teacher "James Cameron" negotiated a "Teachers price" for "Physics": say "25%" of the Subject Price!!
Meaning: It doesn't matter how many students with how many sessions individualy book that Class, "James Cameron" will always get 25% of the Subjects Price multiplied with the sessions in total!!

Scenario A:
So if there are 2 Students, one student booking 5 sessions, the other 10 sessions, the total "income" of the School is 1500$ (5x100$+10x100$)
And "James Cameron" will always receive max 25% of those 1500$ as "outgoing" payment = 375$

Scenario B:
There is only one student, but this one books 15 sessions. the total "income" of the School is again 1500$ (15x100$)
And "James Cameron" will again receive max 25% of those 1500$ as "outgoing" payment = 375$

The teacher doesn't care how many students he has, only how many sessions are booked.

He could even get paid in one single installment, say at the end of a month or year (or whatever)
That's what i meant "he gets paid asynchronously to the students payments"