Hi.
I've searched this but to no avail.
Here are my tables:
T_Classes
CID, Date, StudentName, TutorName (TID)
T_Student,
SID, StudentName, TutorName (TID)
T_Tutor
TID, TutorName
You will see from T_Student, that every StudentName has an assigned TutorName. This is looked-up in the T_Tutor table - no problems so far.
In T_Classes, when I add a new Date and StudentName, I want to automatically "pull" the (current) TutorName for this student from T_Student. Originally, I thought the answer was to join the student and tutor tables but this "solution" permenantly fixes a TutorName to a StudentName - what if the Tutor has been changed since the last class? I only want the current and future entries in T_Classes to reflect this, not the past ones. If somehow I could save/copy the joined field (ie. T_Student.TutorName) to an aliased field in the T_Classes table and use this as the display field?
I have tried playing with Session variables etc. but no luck so far. I've also tried inserting this record using an event - but similarly, no luck.
Can anyone suggest a solution?
Mandy