History query question

[Nyh]

I tried to explain the best I can but it is very difficult to do. Here it goes.

I have three history tables. Teacher, student and school. School and teacher, and school and student are linked together.
I need to make a query that makes in one query a temporary table that list all the students that had a teacher change at some given date in the past.

For example:
If I give the query the following date 01/01/2007 it needs to give me all the student that had a teacher change.
But the table history teacher doesn't have a link with the history student table. So I need to search the history school table to find the student id.

There are three things that can happen:
1st: I give the date 01/01/2007 in so I need to look for this date in the history teacher table. If the school id is not null the teacher hasn't been deleted. Than I need to now if it change from school. I need to do that by searching the change that happened before the date I gave up. If the school id's are identical the teacher is still at the same school. So I only need to look for the first teacher id in the history school table to get the student id.

2nd: If the school id did change I have to look for the first and second teacher id in the history school table to get the student id.

3rd: If the school id is null I need to look for the second teacher id in the history school table.

Sorry for my bad English

Thanks in advance

Nyh