Staffing Calendar reporting.

[cdoyle]

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?

 

[Trevor G]

In your query you can set up a group and then select to show first that will stop duplicates being shown.

When you create a report you can select to add groups, in the design view go to View Sorting and Grouping, select the field you want to group then at the bottom select the group header option

Trevor

 

[cdoyle]

Thanks for replying

I don't think grouping is going to fix the problem I'm running into though,
Using my example above

In my query I have all 3 tables listed, all linked to the in a 1 to many with the employee table.

So since there are 2 work periods listed for Fred (1 with 20 hours and the other with 10 hours).

Then there are 3 entries in the attendance table, so when I run the query each entry displays twice (once for each pay period). If I could group them, and solve this problem. In the report, I don't want the entries from 1/6 showing up in the 1/15 pay period.

Since

 

[cdoyle]

Here is a copy of the db, if anyone wants to see it.

 

[Trevor G]

Whats the name of the table, query and report you want someone to look at.

What version of Access are you using.

Trevor

 

[cdoyle]

the tables in question are going to be the tbl_main (this is the attendance table), employeename, and workhours_tbl

I don't really have a query or report yet, because I don't know how to format it the way I need. The query I started was percentloss_q but it's just a standard select query now.

I'm using Access 2003

 

[DCrake]

Have you heard of WTE (Whole Time Equivelants) This means a person who works for the company on a full time basis has an WHE of 1. Someone who works part time say 20 hours out of a maximum or 40 whould have a WHE of .5. So when you sum the hours a person has worked in a week you can convert this to a fraction of WTE so if they are contracted to work 40 hrs and work 30 then 30/40 = .75 therefore they are .25 under utilised.

Again if a person works 15 out of 20 hours this = .75 but because they only work 20 hours per week this is the pro rarta.

to get the actual % you would mutliply by the WTE

 

[cdoyle]

Have you heard of WTE (Whole Time Equivelants) This means a person who works for the company on a full time basis has an WHE of 1. Someone who works part time say 20 hours out of a maximum or 40 whould have a WHE of .5. So when you sum the hours a person has worked in a week you can convert this to a fraction of WTE so if they are contracted to work 40 hrs and work 30 then 30/40 = .75 therefore they are .25 under utilised.

Again if a person works 15 out of 20 hours this = .75 but because they only work 20 hours per week this is the pro rarta.

to get the actual % you would mutliply by the WTE

Yes I've heard of that, and understand that part. Where I'm having a problem is getting my reports to display the way I need it too.

The staff change their schedules often I guess, so one week they work 20 hours, the next 35 and so on. So I created a new table (workhours_tbl) in the db, where she can update their schedules based on the that period. She needs to be able to keep an archive of their schedule history to use in the report.

So when they call in sick and miss work on a certain day, I need the report to show that the hours they missed to appear in the correct period and then calculates the time lost from that period.

Here is an example again

Fred's schedule
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 not sure how to get a display similar to that above. When I make an entry in the attendance table (tbl_main), it records the event date

How do I get that record in the attendance, to only appear within the correct period in the report like the example?

 

[DCrake]

You may need to create an extra table that holds the employee PK and a week number and the max hours each week then when recording hours you associate the date worked with a week number.

So if Week 2 they have 15 hours scheduled and they only record 10 hours you have both sides of the equasion.
Then in week three if they say they can work 30 hours this is recorded in the schedule table, a bit like forward planning. As long as you group the dates by the weeknumber and sum the hours for that week you can compare it to the scheduled hours.

 

[cdoyle]

You may need to create an extra table that holds the employee PK and a week number and the max hours each week then when recording hours you associate the date worked with a week number.

So if Week 2 they have 15 hours scheduled and they only record 10 hours you have both sides of the equasion.
Then in week three if they say they can work 30 hours this is recorded in the schedule table, a bit like forward planning. As long as you group the dates by the weeknumber and sum the hours for that week you can compare it to the scheduled hours.

I thought about using week numbers, but was hoping to avoid it. The main reason is, not every employee changes their schedule each week. So using a week number, she would have to go in and update every employee each week. Regardless if their schedules changed, I was hoping to find a way a way to eliminate updates to staff who don't change their hours.

If week numbers are the only way to go, how do I pair up the event date in the attendance table, to the week number?

 

[cdoyle]

I've been toying around with this a little using week numbers.

I have 3 queries

The first query gets the week number for the employee schedule using this on the date
Expr2: Format([start_date],"ww").

I then have a second query that gets the week number of the event (calling in sick etc), using a similar formula in the query
Expr1: Format([DateOff],"ww")

Then I have a third query that uses both of the previous queries, that has an inner join on those 2 fields and displays the ones that match. I thought that was sort of working but found that if the work schedule was say for 2009, but they called in sick in 2010 the week numbers still match, and it displays it. Is there a way to make the query tell the difference between years from the 2 tables?

Or is there a much better way to do what I need?

I still don't like the fact, that she'll have to go in every single week and update every single employee's schedule regardless if there is a change or not.

 

[SOS]

Include a YEAR field in the query so it can be linked as well.

MyYear:Year([start_date])

etc.

 

[cdoyle]

Include a YEAR field in the query so it can be linked as well.

MyYear:Year([start_date])

etc.

ah OK, that seems to work.

does anyone have any ideas how to make the updating of the staff schedules any easier?

Maybe a way to automate the upcoming weeks schedules, to duplicate what's already there for the next week, and then she can just go in and modify the ones who actually change their schedules? Still not liking this process though, seems like a lot of duplication. Just not sure of any other way to go about it.

 

[DCrake]

For bulk updating I would first appen dthem to a temp table then bring up the list of employees with the previous/current weeks commitment. Change the ones that need changing then do an append query back to the schedule table with the new year/week number.

 

[cdoyle]

For bulk updating I would first appen dthem to a temp table then bring up the list of employees with the previous/current weeks commitment. Change the ones that need changing then do an append query back to the schedule table with the new year/week number.

Been thinking about this, and how to go about it.
So create an append query from my work_hours table, and move them to my temp table.

How do I filter out only the current work period entries, to move them to the temp table?

for example in my work_hours table I'll have many entries for each employee, since the past weeks are kept.

Not sure how to accurately capture only the last entered week number, to move to the temp table.

 

[cdoyle]

I've been thinking about this some more, would this be the right way to go about it.

Say in my work_hours_tbl if I add a 'active' field, and then in the query that dumps to the temp table. Have it only grab the ones marked active?

Then when I make the changes to the ones in the temp table, before adding those back to the work_hours table, have it mark all the ones currently set to active in the work_hours table to inactive. Then append the data back from the temp table, with these all being marked 'active'?

Does that sound like a reasonable way to do it? Is that even possible, and reliable?

 

[DCrake]

Without completely testing my idea I would personally open a form that had two list boxes on. Placed next to each other the righ hand one would contain the list of all employees who are currently active. The left hand one, inactive. (Some employees might be temps).

Then using custom buttoms to move people from one list to the other I would end up with a list of employees for the next scheduled week/period. I would then havea further buttom that would append these people into a temp table with last weeks hours worked as defaults and then have a subform or some thing similar that would wllow me to edit the hours. Once complete a final button to copy these employees to the schedule table.

Total air code: