Employe work schedule???

[bigdan]

Here what I want to do!!!

I have a access DB with one table for the employe name.
I would like to have another table for the available shift on the week.
Example:
shift 1= monday to friday 8 to 16h.
shift 2=wednesday to sunday at 8 to 16h
etc.

After that I would like to be able to assign every employee to an available shift and this will populate a table for a complete year.
This one need to be editable for shift change during the year.

I was thinking of 3 table.

1 employee
2 available shift.
3 calender. (all the day of the year)

Thanks

Dan

 

[johnherman]

I think you need to have 3 tables:
Employee Table
Employee ID
Employee First Name
etc.

Shift Table (like Calendar by by shifts instead of days)
Shift ID
Start Date
Start Hour
End Date
End Hour
Shift Code (optional Day, Mid, Night, or 1,2,3)
etc.
Remarks

If shifts always start the same time (and always will), the above table can be simplified.

And now the schedule/work table.

EmployeeShift Table
EmployeeID
ShiftID
Status (Scheduled, Present, Sick, Absent, etc)
Other attributes
Remarks

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[BobRodes]

Perfect example of using an associative table to resolve a many-to-many relationship.

 

[johnherman]

Thanks Perfesser.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[bigdan]

I have something on the go right now.
One table for the employee.
One table for the shift/schedule.
I have one record/day/employee
This week i'm off so I'll be working onn it next week.

Thanks

Dan