Find closest value without going over 1

[chrisaroundtown]

Hi,

I have a table called 'Employ Status Change' and another called 'Schedule'.

The table called 'Employ Status Change' shows when employment statuses have been changed. It has an employee ID, the employment status (Permanent, Casual etc) and the effective date.

The table called 'Schedule' has the employee ID, the date of the schedule, the start time of the shift and the end time of the shift.

I need a query to show the 'schedule' table with the employment status appropriate for that date.

For example, let's say employee number 88 was a Permanent employee until 1st Dec 2004 when they became a Casual employee. The query needs to return a Permanent status for all schedules prior to 1st Dec 2004 and a Casual status on and after 1st Dec 2004.

Also, as employees can change their employment statuses multiple times there is the need to look for the entry in the 'Employment Status Change' table that has an effective date closest to the schedule date without going over.

I envisage it working simliar to a DLookup where it find the employment status from the 'Employment Status Change' table that has the effective date closest to the schedule date without going over.

Please let me know if you can provide any help or if you need more information.

Thanks
Chris

 

[SarahG]

I have made up the field names, but you should be able to follow:
1. You need to equi-join the status table(I will call it Status1) to the schedule table, joining on Employee id.
2. Then add the status table a second time to the query (Status2). Create an outer join between 'Status 1' and 'Status 2', i.e. all records in 'Status 1' and those that match in 'Status 2', on Status1.NewStatus = Status2.OldStatus AND Status1.EmployeeID=Status2.EmployeeID.
I hope you are with me so far!
3. Then you select Status1.NewStatus and your schedule information to be outputted.
4. Your criteria is as follows:
schedule.date >=Status1.ChangeDate AND scheduledate < iif(isnull(Status2.ChangeDate),Now(),Status2.ChangeDate).

Where ChangeDate is the date the status changed.

Basically you will use Status1 to pull the start of status period, and 'Status2' to pull the end date (if there is one).

I hope this is clear....Good luck!