Identifying a Student's Last Job
I need to gather some data about a student's last job. Their jobs are tracked in a Job table with a job_id as the primary key, and the student ID as a foreign key. The context for the Job layout (with all jobs) is the Job table. The context for the Last Job layout is the Student table.
To identify their last job, I defined a calc in the Job table, like so:
cLastJob = Max ( job_id )
The idea was that the highest job_id number for each student would correspond to their last job as tracked in the database.
This calc picks a job, but for a student who had two jobs, it's not picking the one with the highest job_id.
Should I be approaching this task in a different way?