Need help only selecting one related record for a report
I need help figuring out a way to select only the most current related record for each individual listed in a report.
I have a table listing each employee. I have a related table that lists their job title. In that table there is an effectie date of the job title. They may change job titles over time.
I need to create a report listing each employee, list only their most current job title, and sort by job title.
Currently my report lists each employee and each of their job titles.
I don't want to force the user to enter an end date for the previous job title, nor do I want to force them to check a box indicating which job title is their current one as it would require them to uncheck previous records.
Anyone body have any ideas on a good way to approach this?
Philmodjunk.... you are always a good idea person. Thoughts?