last date (1 Viewer)

willem

Registered User.
Local time
Today, 22:32
Joined
Jun 17, 2001
Messages
41
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).]
 

Liv Manto

Registered User.
Local time
Today, 22:32
Joined
Apr 26, 2001
Messages
266
on your query create this field.

DatePart: DatePart("m",[student]![Date])

Where DatePart is name of the field,
m is the month, student is the table and date is the field.

On the same field, create criteria


<DatePart("m",Now())

where now() is the current month.

Then on another field, just drag the date field.

It would show all dates that are before the current month. Then group them together.

So:

First Field

Student
GroupBy

Second Field

DatePart: DatePart("m",[student]![Date])
Last
Criteria: <DatePart("m",Now())

Third Field

Date
Last
 

Users who are viewing this thread

Top Bottom