Need Help with staffing calendar reporting

[chrisfromwa]

I'm not sure if this should go in the reports area, or the general area, but
I'm creating a staff calendar type database for a friend of mine, and having some problems with how to create the type of report she needs.

Basically because her staff are part time, the number of hours they work during a given week could vary.

For example 1 week, they could work 20 hours and the next week they could work 30. I guess depending on the employee it changes pretty often.

So she needs a way to have a report to calculate the time loss, for when they call in sick etc.

Since their work weeks vary so much, I'm having a hard time figuring out how to go about this.

Here is my table structure so far, hopefully this is the right way to go about it. One thing I would like to avoid is making it so she has to create a new record in the schedule table for every employee, each week if an employee doesn't change the number of hours. I thought maybe having an open end date like 12/1/2099 so it will be active until she put an end date, and creates a new record. I think that will cause problems later on in the report. Any Ideas?

Employee_tbl
id << primary key
name

schedule_tbl **This table keeps the history of their weekly work schedules
id << primary key
employee_id
scheduled_hours << number of scheduled hours
start_date
end_date

attendance_tbl This table keeps track of exceptions (call in sick)
id << primary key
event_date
employee_id
reason_code


So she wants to be able to create a report that will take the event date, and match it up to the correct work schedule week. Then it will display a calculation of the work loss for that schedule period. I'm just unsure how to do this, not sure how to go about this.



I'm not sure how to write the query, or the report.

example:
Fred
1/1/2010-1/7/2010 Fred is scheduled to work 40 hours
1/8/2010-1/15/2010 Fred is scheduled to work 10 hours.

Fred called in sick on 1/5, 1/6 and then again on 1/14.
both days he missed 8 hours.

so I would need a report that displayed something like this

1/1/2010-1/7/2010
Fred 1/5 PTU 8 Hours
Fred 1/6 PTU 8 Hours
Total Hours Scheduled: 40 Missed Hours: 16 Time Loss Percent: 40%

1/8/2010-1/15/2010
Fred 1/14 PTU 8 Hours
Total Hours Scheduled: 10 Missed Hours: 8 Time Loss Percent: 80%


I'm just getting stuck on how to build the query (I keep getting duplicates) and then in the report how to go about grouping etc.

Anyone ever try anything similar to this or have any ideas on how to accomplish it?

[chrisfromwa]

Here is a copy of what I have so far.