I have two related tables. One with student id, name, ....
The other with projects and startdates. A student can only participate one project at a time. It is to keep track of students and their projects.
Now i need to select the students for one month and one project. It is possible for a student to be in the project when the startdate is before the beginnning of the month and there is no startdate in between.(and/or it is possible that he changed projects during the month, during the month he can change back to his old project).
So i need to see whether a student was in the project before the month started. It has to be a startdate that is the highest of all dates before the beginning of the month. How do i do that in sql.
The rest of the query is simple but the part of the highest date before the beginning of the month. I tried the Max function in a totals query but it produced a record containing the highest date that had the right project bound to it. So there was a record that had a higher date but that had not the same project.
[This message has been edited by willem (edited 08-12-2001).]
[This message has been edited by willem (edited 08-12-2001).]
The other with projects and startdates. A student can only participate one project at a time. It is to keep track of students and their projects.
Now i need to select the students for one month and one project. It is possible for a student to be in the project when the startdate is before the beginnning of the month and there is no startdate in between.(and/or it is possible that he changed projects during the month, during the month he can change back to his old project).
So i need to see whether a student was in the project before the month started. It has to be a startdate that is the highest of all dates before the beginning of the month. How do i do that in sql.
The rest of the query is simple but the part of the highest date before the beginning of the month. I tried the Max function in a totals query but it produced a record containing the highest date that had the right project bound to it. So there was a record that had a higher date but that had not the same project.
[This message has been edited by willem (edited 08-12-2001).]
[This message has been edited by willem (edited 08-12-2001).]