Is there a formula? or...

[Guest]

I currently have a database that tracks the number of students who participate in our after school program. It counts how many times a students has participated in our program and also points out what programs each student has participate in. We have an after-school program where students receive additional help from our on site tutors. The tutors on our database have a unique ID on a seprate table eg. 401 = Jon Do

1. One of my co-workers would like to have a list of students with their RECENT tutors

Problem that I am experiencing is that the students see more than one tutor so if I run a querie I would recieive a huge list of students with all the tutors they have seen since the program started. So my question is.......Is there any way to find out the students current tutor despite the fact that they had different tutors in the past. I tried doing this by a criteria (date) , but that would neglect some of the students who have participated prior to that specific date.

any ideas?

 

[fredg]

I currently have a database that tracks the number of students who
participate in our after school program. It counts how many times
a students has participated in our program and also points out
what programs each student has participate in. We have an
after-school program where students receive additional help from
our on site tutors. The tutors on our database have a unique ID on
a seprate table eg. 401 = Jon Doe

1. One of my co-workers would like to have a list of students with
their RECENT tutors.

Problem that I am experiencing is that the students see more than
one tutor so if I run a querie I would recieive a huge list of
students with all the tutors they have seen since the program
started. So my question is.......Is there any way to find out the
students current tutor despite the fact that they had different
tutors in the past. I tried doing this by a criteria (date) , but
that would neglect some of the students who have participated prior
to that specific date.

any ideas?

Here is a basic Group By query. You'll probably want to join the Tutor
table and the Students table (I assume you have them) to add the
Student and Tutor names to the below query.

SELECT tblBasicData.StudentID, tblBasicData.TutorID,
tblBasicData.ADate
FROM tblBasicData
WHERE tblBasicData.ADate=DMax("[ADate]","tblBasicData","[StudentID] =
" & [StudentID])
ORDER BY tblBasicData.StudentID;