• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

creating database (1 Viewer)

Vahram

New member
Local time
Yesterday, 21:06
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
Yesterday, 21:06
Joined
Oct 29, 2018
Messages
12,553
Hi. A good database design starts with the table structure. Can you share your table structure with us?
 

Vahram

New member
Local time
Yesterday, 21:06
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, 00:06
Joined
May 21, 2018
Messages
3,496
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
Yesterday, 21:06
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
Yesterday, 21:06
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: 30
  • Capture1.PNG
    Capture1.PNG
    6.4 KB · Views: 30
  • Capture2.PNG
    Capture2.PNG
    8.7 KB · Views: 18
  • Capture3.PNG
    Capture3.PNG
    6.7 KB · Views: 18
  • tbl_emp.PNG
    tbl_emp.PNG
    13.3 KB · Views: 17

Users who are viewing this thread

Top Bottom