Find greatest value in related table
Fairly inexperienced with FM & databases in general so please bear with me...
I have 2 tables for tracking employee data. Primary table (Employees) for the employee's general info and secondary table (Status) for tracking the employee's dates that he/she was active/inactive with the company.
These tables are related based on primary & foriegn keys.
In the secondary table are a status field and an date field. The employee is hired the status "Hired" is entered in the status field and the date hired is entered in the date field. When the employee is terminated a new record in the Status table is created that reflects the "Terminated" status. This allows a history to be created so that an employee's total time working for the company can be calculated.
One of the things I need to to is:
Search the Status table and find the most recent date for an employee's status and return that status. So, if the employee has come and gone several times I need to know what his current status is and the date of that status.
When I created the relationship between the tables I sorted the status table by the status date in decending order. This retuns the most recent status when callled from a form but I need to create a report which will return the most recent status for each employee. This dosen't seem to work the way I expect.
Another thing I'd like to do is:
Calculate all of the time the employee has been active. This would entail counting the time between each period of hired to terminated, hired again to terminated again, to hired to still active. This employee may have worked a total of 2 years over a period of 5 years and has recently begun working for us again say 3 months ago. This should add up to a total of 2 years & 3 months since he was first hired 5 years ago.
I hope this makes sense and that someone can provide some insight & guidance.