Query Input

[Secret Squirrel]

I need some input to help create a query between two of my tables.

The first table is structured like this:

tblBusinessHours

ShiftID - PK
Shift - Text
StartTime - Time
EndTime - Time
EffectiveDate - Date
EmpID - Number

The above table is used to create business hours for our hourly employees.
These can change depending on the business workload. That's why I have an
effective date in the table. I also have the employeeID because some
employees have their own custom hours (no I don't have custom hours, I'm 24/7
on call). They also have an effective date since this can change from time to
time.
And the second:

tblTimeAttendance

RecordID - PK
Shift - Text
EmpID - Number
PunchDate
TimeIn
TimeOut

This table is imported from our time clock with the daily punches for every
employee. The information is pretty basic and self-explanatory.

What I need to do is create a query to combine these two tables but I need
to use the "StartTime" and "EndTime" for the employees that have their own
business hours set in the tblBusinessHours. And they also need to use the
hours that in effect. Which means if the effective date is 12/01/08 then they
need to use their hours that start on 12/01/08. If it was prior to that then
the would have to look back to another record in the tblBusinessHours that
has their EmpID and where the punchdate is greater than or equal to that
effective date. And for all our employees that do not have their own record
in the tblBusinessHours then they have to use the record that matches the
shift from the two tables and then also use their punch date which is greater
than or equal to the effective date for that record.

I hope I made sense. Can anyone shed some light on how I can set this up?
I'm having a brain freeze today and can't think it through correctly.

Thanks
SS

 

[S.Clark]

I'm assuming you only want the max effective date, thus, create a query to
pull the max effective date per employee. From there link it into an append
query that performs the work you need done, and writes the results to a temp
table.

Then, for those emps that do not have records in the tblBusinessHours, run
another append query to append to the same temp table.