I have a database that tracks academic appointments within a university department. A single person could be appointed to a number of different titles over the years – a Teaching Assistant one semester, a Researcher another semester, a Lecturer for a full year, etc.
The database also tracks the many steps within the (extremely bureaucratic) hiring process, because multiple people within multiple departments are involved in hiring from start to finish.
Each single appointment (e.g., John Smith, researcher, Fall 2016) has a record in the Appointments table. There is a related table, Status Updates. The Status update table has __kpStatusID as its primary key, with _kfAppointmentID as the foreign key. When the status of an appointment is updated, a new related record is created in the Status table that describes the update. Status updates are displayed in reverse chronological order in the Appointment’s record, in the Appointment table, via a portal. For example:
8/7/16 Hire completed
8/5/16 Appointment entered into payroll
8/2/16 Appointment sent to HR for processing
8/1/16 Appointment initiated
I would like to create a report that lists all pending appointments and their most recent update (“sent to HR,” “sent to Payroll,” etc.) – essentially, all appointments that do not have a related record in the Status table with a status of “Hire completed” (but may have multiple related records that show other updates).
I am totally stumped on how to create this report, which would, I guess, pull the most recent related record entered in the Status table, and would omit records whose most recent entry is “Hire completed”? It’s the pulling the most recent update, and only the most recent update, that’s confusing me.
Thanks for any help!