creating database (1 Viewer)

Vahram

New member
Local time
Today, 03:35
Joined
Apr 24, 2020
Messages
10
I would like to create a database to schedule 20 workers for two shift daily Monday-Friday on a monthly bases and output to the calendar, considering US holidays as none working days, which should be an option to enter in the database. Also some of the workers are not working on Mondays and some are not working on Fridays (Also to be option to enter), Vacation and sick time to be entered as well, so workers can be excluded to be scheduled on those days. And finally each worker to have preference of the shift.

I need help on the approach to this project.

Thanks in advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:35
Joined
Oct 29, 2018
Messages
21,357
Hi. A good database design starts with the table structure. Can you share your table structure with us?
 

Vahram

New member
Local time
Today, 03:35
Joined
Apr 24, 2020
Messages
10
I haven't started yet, I want to know how to separate the info that I have through out the tables so then I can establish the relationship between tables and work out the detailed
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:35
Joined
May 21, 2018
Messages
8,463
TblEmployees
EmployeeID ' autonumber field
ShiftPreferece_FK
.... Fields unique to employees FirstName, LastName, HireDate, etc.

TblShifts
ShiftID 'autonumber
ShiftName ' Shift One, Shift Two, Vacation, Sick .....
other fields describing what a shift is maybe Start time, End time, Notes

TblEmployeeShifts ' many to many table
EmployeeID_fk 'foreign key to employee table
ShiftID_FK 'to shift table
shiftDate 'Date field
 

Vahram

New member
Local time
Today, 03:35
Joined
Apr 24, 2020
Messages
10
TblEmployees
EmployeeID ' autonumber field
ShiftPreferece_FK
.... Fields unique to employees FirstName, LastName, HireDate, etc.

TblShifts
ShiftID 'autonumber
ShiftName ' Shift One, Shift Two, Vacation, Sick .....
other fields describing what a shift is maybe Start time, End time, Notes

TblEmployeeShifts ' many to many table
EmployeeID_fk 'foreign key to employee table
ShiftID_FK 'to shift table
shiftDate 'Date field
Thank you.
Let me work on it.
 

Vahram

New member
Local time
Today, 03:35
Joined
Apr 24, 2020
Messages
10
I have created calendar table with all US holidays and weekends as none working days to use for scheduling. Attached are the tables that I created and the relationships. I would like RANDOMLY schedule 22 workers for two shift daily Monday-Friday on a monthly bases.
I need help creating query randomly selecting two workers for each day considering their RDO and Void dates and Shift Preference.

Your help is much appreciated.
 

Attachments

  • Capture.PNG
    Capture.PNG
    25.4 KB · Views: 148
  • Capture1.PNG
    Capture1.PNG
    6.4 KB · Views: 164
  • Capture2.PNG
    Capture2.PNG
    8.7 KB · Views: 154
  • Capture3.PNG
    Capture3.PNG
    6.7 KB · Views: 129
  • tbl_emp.PNG
    tbl_emp.PNG
    13.3 KB · Views: 141

Users who are viewing this thread

Top Bottom