Need Help building a databse to track & automate schedules

[bddewell408]

Request Help Designing a Database

I am trying to build a database to track schedules and automatically schedule workers for supervisors. My problem, our company has nine different rotations per shift, six shifts (day shift, evening shift, midnight shift/split between north and south). We also work a five days on, three days off type rotation except for the 14 day rotation, which works the standard workweek. At least we don’t have to worry about rotating shifts, we are assigned shifts based on seniority permanently for 1 year. Luckily, Excel can duplicate patterns, and quickly, and I have already got the base information of days off per rotation through 2010, and then imported that into Access.

Here are the tables I have designed or thought about designing so far:

Table 1: Employee Information
Employee ID – Primary Key 000-00-0000
Employee Name John Q Employee
Rotation Number 1,2,3,4,5,6,7,8,14 day
Shift Assignment Mid South, Mid North, Day South, etc

Table 2: Scheduletableversion1
Autonumber –primary key 000000000
Date Sample Data 00/00/0000
Rotation Number Sample Date 1,2,3,4,5,6,7,8, 14 day
Leave Code Sample data Null (means working), D (Day Off), AL (Vacation), SL (Sick Leave), etc

Table 3: Scheduletableversion2
Date- Primary key 00/00/0000
Rotation 1 Null, D, AL, SL
Rotation 2 Null, D, AL, SL
Rotation 3 Null, D, AL, SL
Rotation 4 Null, D, AL, SL
Rotation 5 Null, D, AL, SL
Rotation 6 Null, D, AL, SL
Rotation 7 Null, D, AL, SL
Rotation 8 Null, D, AL, SL
14 day cycle Null, D, AL, SL

Table 4: Employee Leave Table
Autonumber – Primary Key 00000000
Date 00/00/0000
Employee ID 000-00-0000
Leave Code Null, D, AL, SL

Table 5: Scheduletableversion3 (This is simply table2, with table 4 added to consolidate the data)
Autonumber – Primary Key 000000000
Date 00/00/0000
Rotation Number 1,2,3,4,5,6,7,8, 14 day
Employee ID 000-00-0000
Leave Code Null, D, AL, SL


My trouble comes from how to combine the information. I know there must be a way to extract the information using queries (I do not know Visual Basic, but would be willing to try if that is what anyone thinks it will take.)

What I want to try to do is create a base parameter query so that a schedule will be brought up by date on an Access Form.. Hopefully the below diagram will help.


Schedule for [date from parameter query]

[query of all employees working, null value from schedule table and then employees linked by rotation number (if that is possible)]

[Query of all employees not working and the code reasons why]

I can do the next myself, I am going to have 25 unbound text boxes to enter where employees will be working, and plan to protect the above queries so they can not be modified.

I plan on creating many queries to divide everything north/south then day/evening/midnight.

I was hoping to build something that would never need maintenance, but seeing as how employees rotate shifts, rotation numbers, and leave, I will probably need to archive the data every year at the shift change.

If anyone thinks this is also too hard, let me know that as well.

 

[willir]

bddewell408

You have two issues, and both required that you go back to your table design and relationships.

The first is the easier issue, and more important - table design and relationships.

I start from the output end -- can I get the reports I need using this design. For you, it looks like you need to track and predict schedules. But work out the details -- employee, shift, days stuff.

Then see if you can produce these reports using your table design. This will force you to plan what queries you will use, and how to link the tables. (I will use index cards or a white board for this step.)

Then review the sublties -- how to prevent duplcate data and orphans -- things that can affect data integrity.

This is where I found it really pays off to invest time. For example, when defining a foreign key in a table, I will setup a query in the lookup tab. For your employee leave table, SELECT employee_name, employee_ID from employee and bind column two. This steps saves time because when designing the form, the system automatically creates the combo box.

Also use the relationship design to graphically depict your relationships. (You may have to hold off on enforcing referential integrity until the next step.)

The next step is harder -- importing data. Some of it will be easier, other parts harder. Using the employee leave table, provided the design stuff has been done. After importing the employee table and the employee leave table, the two should be linked on employee id.

But setting up the scheduling components will be harder. You may have to get into a bit of coding for this.

Oh yea. Watch out for many-to-many (M:M) relationships. If not done correctly, this may hinder the usefullness of your database. Frm your description, I can not tell if and where youmay run into this issue.

But here is a scenario.
A high school kids takes various courses
A teacher teaches several course
A course can be held in various class rooms
A room can have various courses.

There is a M:M relationship between kids and teachers, and between class rooms and courses. Where a school would have a one-to-many relationship with class rooms, and one-to-one relationship with school to principle.

For the M:M relationships, you will need to build a join table.

student_id
teacher_id

The primary key would be using both fields together.

Your design excercise probably made you think about this.

I hope I have partially answered your questions.

Anyway, good luck
Richard