Show current job for an employee
I have an Employee Table, Job Table, and Attendance Table all related by Employee_ID. The job table tracks the employees jobs throuhgout his/her career which can be many jobs. It also tracks the Start and End Dates for each job. The Jobs Table is in a portal on the Employee form, this allows the addition of future jobs.
Out of all the jobs for an employee I need to show the current job Title in the employee form and in the attendance form, but not in a portal.
I only see the first record out of the many that are tracked on either form by using the Title field. I don't know why this happens or how to stop it from happening.
What method would be best suited to do this?