Database for employee scheduling training, vacation, etc.

[wvmbark]

I'm trying to set up a database for scheduling and tracking employees' training, vacations, sick days, etc. Two things I'd really like to get out of the database...

1. Report where Rows would be employees and columns would be days "1/1/06", "1/2/06", etc. The values would be Start and End Times if the employee is scheduled to work, "Training" or "Vacation" if the employee is in training or on vacation those days. Basically like the Excel spreadsheet that is currently being used.

2. On a form for entering training dates for an employee, I'd like to be able to recognize that an employee is scheduled for vacation that day or that it's not a "scheduled work day", etc. Perhaps his name wouldn't even show up in the combobox because the query recognized he was on vacation?

Basically, the employees work the same shift all year. They bid for vacations for the year and once the vacation schedule is set, it changes very little if any until the next years determinations. Also, each employee is required to take 2 weeks of technical training each year. If an employee is scheduled for training, an employee from a "relief" crew moves into his schedule for those two weeks. Most training is scheduled at the beginning of the year for the year. Currently everybody's schedule is laid out in spreadsheet where each column is a day and each row is an employee; this is used as a tool for plotting vacations and trainings, and determining where shift holes are and moving "relief" crews into those slots, etc.

I realize this is very vague, and of course I'll want to do a bazillion other things with the database. But, I'm really just trying to get some ideas on the possible ways to get started with setting up my tables.

I have tblEmployees (EmpID, Name, ShiftCode, Dept, other) where the ShiftCode identifies the shift description of the employee.

I also have tblShiftCodes (ShiftCode, Description, MonStartTime, MonEndTime, TuesStartTime, TuesEndTime, etc for all 7 weekdays). But, this just doesn't seem like the best way to go about it.

And there's tblTraining (EmpID, TrngID, SchedStartDate, SchedEndDate, Completed y/n)

And, I could create a table for vacations, etc.

Ultimately, I need it set up so that I can view any day and see which employees are where - scheduled for work, vacation, off, etc.

Again, I'm currently just looking for a table structure that will allow for all of this and that I'll be able to develop over time (and as my Access skills develop). If anybody has a database that is used for similar stuff, perhaps I could sneak a peak at that.

Thanks for any assistance!!

Mike

 

[MajP]

Mike,
Spend some time searching this site. There was a lot a discussion about a month ago dealing with handling shift schedules. I am sure that are a lot of similiar applications out there. One thing I was thinking was that Vacations and Training are exceptions to normal shifts. I would have a table holds this information. It has a person ID, reason for being absent (vacation or training), start date, and stop date.

tblPersons_ScheduledAbsence
intPersonID
strTypeAbsence (Vaction or Training or other)
dtmStartDate
dtmEndDate

So I think you need your table shift codes, but also this table. Many people can have the same shift (thus your one to many relationship that your describe), but training and vacation dates are unique to the person.

 

[wvmbark]

Majp,

Regarding the ShiftCodes tables, how I take a table that has the "normal" weekly schedule and produce a result that shows that schedule spread over a 1 month, or 1 year interval? And, is having a different field for each weekday's schedule (as laid out in my first post) the best structure for this? I guess what I'm asking is: is it easy to take a schedule (Mon-Fri 8-4) and display that over a 1 year time period (Sun 2/1: "null", Mon 2/2: "8-4", Tues 2/3: "8-4", etc for the entire year... or until infinity for that matter)?

Do I need to create a table with one field that has all dates for the year in it, and then use that in a left join query?

Or do I need to actually enter every person's schedule for every single day?

Thanks again!

Mike

 

[dhookom]

There are some calendar style reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

There is a "recurring" scheduling demo at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I would never create a table like your suggested tblShiftCodes. Consider normalizing the table. I agree with MajP regarding the absence table.

You should really attempt to ask one or two very specific questions at a time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]