I have two tables. One is the -reqtable- in which i have
ID_request, PK
ID_Student,
ID_Professor,
Date,
ID_TypeofReq 1 meaning 'change mentor' , 3 meaning ' rejected by mentor'
ID_approved-rejected 1 menains approved , 2 meaning rejected
The other table is STUDENTStable:
ID_STUDENT as primary key
ID_Professor acting like a foreign key but not directly connected to any table.
The idea is to get the current mentor for each student. Students can change mentors and I want to always have the current one. I tried to achieve that with query but I didn't get the right results jet.
So I got an idea to to make a query that will update the ID_professor in the table STUDENTStable and I want the query to be that connection.
Type of request-> ID_typeofreq can be 1 meaning 'choosing mentor' and if the professor approves (ID_approved-rejected = 1) then the student has its mentor.
Type of request can also be 3, which means that mentor was mentoring the student but doesn't want to mentor the student anymore (the id_approved-rejected in that case is always 1 meaning approved). If that is the last entry than the student doesn't have the mentor anymore and should not be in the result of 'currently mentoring query'. But the student can later choose a new mentor and if is accepted he will get into the result 'currently mentoring query' again
The two tables are not connected, but its no problem to join them if will do the job.
SELECT a.ID_request,
a.ID_Student,
a.ID_Professor,
a.Date,
a.ID_typeofreq
FROM [reqtable] AS a
WHERE (((a.ID_typeofreq)=1 Or (a.ID_typeofreq)=3) AND ((a.ID_approved-rejected)=1)
AND ((a.Date)=(SELECT MAX(b.date)
FROM [reqtable] AS b
WHERE b.ID_request = a.ID_request)))
ORDER BY a.ID_Student DESC;
I need the code that will catch the last entry and if the type of request is 1 and ID_ aprooved-rejected = 1 to put the new id_professor in the STUDENTStable
And if the last ID_typeofreq is 3 and ID_aprooved-rejected = 1 to set the value of ID_Professor to Null again.
