Of Several Possible Entries - Need only the latest - and its related fields

  • Thread starter Thread starter Just Me
  • Start date Start date
J

Just Me

I've been looking and trying several different things, but so far
nothing that works.
My students all have a student_code and are enrolled in certain
classes (class_ID). This data comes from
tblStudent_Program_Enrollment.
Same students also have an activity table (Student_Activities)that
consists of
the activities that each are involved in (activity_code), the date of
activity(activity_date), and which teacher worked with them,
(instructor_ID_)on that activity.
*
I need to be able to separate out the student_Code, class_ID,
Activity_Code, the teacher that worked with them - and all I want is
the last one. That is, I only care about data relative to the most
recent activity_date.
I want to see the last activity, its date, and the teacher who was
involved relative to each inidividual student. Does that make sense?
*
So far, if there has been more than one instructor or more than one
activity it has given me a date, service, and instructor for each
activity. I only want one line per student.
*
I am using Access 2000. Is what I am wanting to do even possible?
Sure seems like it should be, but I haven't had any luck.

I know everyone is very busy so I truly do appreciate any assistance.
Thanks much!
D
 
This is such common request that it is one of my interview questions I ask
when I am hiring access developers!

You don't mention where you want to send the results, but lets just assume
you need the query that shows the info, and then you can send it off to a
report, or whatever.

The first thing is simply to build a query that joins the two tables. We
will use a left join, since some students will not have activities. Just
fire up the query builder and drop in the two tables, and then draw a join
line between the two tables. (you always start the join line from the parent
table, and work your way down.

if you build he above query, you will get something like:


select studentID, studnet_Code, class_ID, activity_Date, instructor_id
from tblStudent_Program_Enrollment
left join tblStudent_Program_Enrollemnt.studentID =
Student_Activtities.StudentID

The above will of course list all of the activities for a given student. Try
using the view command to see how well the above works. You don't have to
write these queries by hand, or least the starting part. Even after years of
using sql, I still tend to use the query builder, and my brain is not that
good.

Now, the next thing is simply to throw in a condition that only allows ONE
of the detail ( in this case activity). And, we simply set the conditions to
retrieve only the LAST (most recent date). So, in the condition field for
student_Activiteis.StudentID, we will use:we simply add to the above:

(select top 1 student_Activities.StudentID from Student_Activities
where studentid = tblStudent_Program_Enrollement.studetnID
order by activity_Date DESC, id)

So, you do actually have to type in the above query condition, and you type
the above INTO the condition box for that studentID field. If you actually
look at the sql, then you get:


select studentID, studnet_Code, class_ID, activity_Date, instructor_id
from tblStudent_Program_Enrollment
left join tblStudent_Program_Enrollemnt.studentID =
Student_Activtities.StudentID

where student_Activities.StudentID =

(select top 1 student_Activities.StudentID from Student_Activities
where studentid = tblStudent_Program_Enrollement.studetnID
order by activity_Date DESC, id)

I kind of guessed the field names here, but if you use the query builder,
then that should not be a problem.
 
Back
Top