Employee Scheduling

[SoonToBeRetired]

Good Afternoon,

I am working on solving a scheduling problem for a company. Here are a few facts:

The company begins projects up to several times each week. This year they have 75 projects that have either already started or will start before the end of the year. Regardless of when the project is completed, all are considered 2018 projects if they begin on any day in 2018.

Each project is composed of a predefined set of tasks.

Each task can require a different number of mechanics with different specialized skill sets. However, any mechanic with the needed skill set can work on any task the needs their skillset.


Each task also has to be performed in a specific workshop and requires specific tools.

Some tasks require the same tools and some do not. Tools are not viewed as pre-packaged kits, but as a individual needs for a specific task that is being performed.

In order to maximize scheduling efficiency for mechanics, workshops, tools, each project can follow one of 8 different project designs.

The projects can last from 2 hours up to several hundred work days.

Tasks can last from 15 minutes to 8 hours.

Work days are 8 hours long with a 1 hour lunch break.

Some work days are 12 or 14 hours long. However each of these days occurs at a specific time depending on which project design is being used.


I am able to create a composite work schedule for projects. The company only works Monday thru Friday and takes off recognized holidays. Since the date that a project begins is scheduled in advance we are able to easily develop a daily schedule of tasks to be completed using simple SQL queries.


The difficult part, and the reason for my post, is determining the minimum number of mechanics that the company needs to have on duty each day to cover the tasks that are scheduled. As well as determining the hours that we need to schedule those mechanics to work each day.



I have used basic SQL to determine which hour long time periods during the day require mechanics to be on duty and how many need to be available during each specified time period. However, this does not answer the ultimate question, "What is the minimum number of mechanics that need to be on duty each day?"




Has anyone ran into a similar situation? Does anyone have any recommendations on how to calculate this number?

[zmbd]

What have you tried so far, you mention a partial SQL solution, can you post the SQL script (please use the [CODE/] format tool)?

While we're happy to help, we do need to have you do the initial work so that we have a better understanding of what is you are after and the environment within which you are working otherwise you may get a Fortran or COBOL solution when you need C++ or SQLLite when you Need an Oracle...

[SoonToBeRetired]

Hello @zmbd sorry for the lack of information. Using MS Access. Below is the sql query that I am using to come up with the number of staff that we need for the different hours of the work day.

Just for the sake of clarity, this code does not lead me what I am seeking. While this tells me the number of mechanics that are needed per hour, it does not take into account the overlap that mechanics can work. For example, a mechanic who is needed for 8:00 to 10:30 for one task can also be assigned to work on a task from 3:00 until 5:00.

In addition, based on company rules and the need to complete administrative work, mechanics are only scheduled to work on task completion 7 hours each day. The other 1 hour of their shift is spent on administrative work.

SELECT tbl_work_schedule.task_work_date AS [Date], 
Sum([tbl_work_schedule].[0800 - 0900]*[tbl_work_schedule].[team_1_mechanics_required]) AS [0800 - 0900], 
Sum([tbl_work_schedule].[0900 - 1000]*[tbl_work_schedule].[team_1_mechanics_required]) AS [0900 - 1000], 
Sum([tbl_work_schedule].[1000 - 1100]*[tbl_work_schedule].[team_1_mechanics_required]) AS [1000 - 1100], 
Sum([tbl_work_schedule].[1200 - 1300]*[tbl_work_schedule].[team_1_mechanics_required]) AS [1200 - 1300], 
Sum([tbl_work_schedule].[1300 - 1400]*[tbl_work_schedule].[team_1_mechanics_required]) AS [1300 - 1400], 
Sum([tbl_work_schedule].[1400 - 1500]*[tbl_work_schedule].[team_1_mechanics_required]) AS [1400 - 1500], 
Sum([tbl_work_schedule].[1500 - 1600]*[tbl_work_schedule].[team_1_mechanics_required]) AS [1500 - 1600], 
Sum([tbl_work_schedule].[1600 - 1700]*[tbl_work_schedule].[team_1_mechanics_required]) AS [1600 - 1700], 
 
FROM tbl_work_schedule
 
GROUP BY tbl_work_schedule.task_work_date
 
HAVING (((tbl_work_schedule.task_work_date) Between [Forms]![frm_team_1_mechanics_required]![txtbox_start_date] And [Forms]![frm_mechanics_required_]![txtbox_end_date]));

[zmbd]

+ I've moved your thread to the Access Forum so that you'll get a bit more traffic on the question.

!! There are several "project" planning/scheduling software platforms available in the market place, have you looked into these? Yes, I know that these can be pricy; however, what you are attempting is fairly complex combining assets, resource, and employee scheduling databases into one application - not for the faint of heart and will in the long run be beyond what we can do for you. One of these applications may suit the bill better than an in-house development

+ Looking at your SQL you have fields with names such as ].[0800 - 0900]*[ which leads me to question the level of normalization within your database.
This lack of normalization may be the roadblock in getting your staffing levels.

+ Can you give us a general layout of your tables?
[Table1] with 1:M with [Table2] and M:1 with [Table3]
(field list)
[Table2]
(field list)
[Table3] with M:1 with [Table1]
(field list)
etc...
At minimum it sounds like you need (other Experts will help refine or have suggestions for these) ((tables names such that [This to That] are a linking table between the two:
- [Projects]
- [Project Design] <<< Need a bit more detail about this, you have 8 different ones; however, we have no detail about how these actually relate to your work flow
- [Tasks]
- [Mechanics]
- [Skills]
- [Projects to Project Design]
- [Tasks to Skills]
- [Mechanics to Skills]
- [Projects to Tasks]
- [Tools]
- [Workshops]
- [Tools to Workshops]
- more tables to deal with the calendar aspect... I'll have to think about these a bit more and the Holiday thing adds a chink

(also the field names in your SQL are a bit wonky, while allowed, I highly advise against anything other than Alphanumeric and the Underscore in field/table names - no spaces and no special characters outside of the underscore.
You'll also want to avoid using Names for field, table, query, VBA Procedure, Form ,Form Controls, and Variable such as "Name" and at least the ones listed here: http://allenbrowne.com/AppIssueBadWord.html

[PhilOfWalton]

I'm very much in agreement with zmbd that your project whilst probably do-able, is going to be extremely difficult to do using Access. There are too many variables.

Suppose there are two projects, Project 1 and Project 2. we have a timeline for both projects and find that a specialist tool is required for both projects at the same time. So we pause the start of Project 2 so that the tool requirements no longer clash. As a result, where there was no conflict before, the need for specialist mechanics clashe. So another variable, do you add another pause to the Project 2 or get hold of a second mechanic?

Bearing in mind you are "SoonToBeRetired" I guess that happy event could happen well before your project is completed.

Phil

[NeoPa]

Would I be correct if I assumed that the maximum number of mechanics required for the day would be equal to the largest value of mechanics required per hour?
I suspect not. However, if that isn't true then I don't believe you have the information available to calculate or determine what the actual value is.

You can only calculate with the information available, so that's critical to what results you can even look for. I hope that makes sense.

[Rabbit]

If you have the number needed per hour for a day, then the minimum number needed for that day should be the maximum number for any hour in that day.

[zmbd]

what-if
There are multiple projects concurrent to each other...
Would that mean then that the largest Sum() of the number of mechanics for any given hour within the day be the minimum staffing? Does this change if the projects cross shift schedules - notice OP has the staffing per scheduled shift glitch in there...

[NeoPa]

I'm thinking of a scenario where a single engineer with skillset A starts the day and works for one hour only. Later that day two engineers are required with skillset B. The first engineer doesn't have skillset B so we need three engineers at least - even though the maximum for any individual hour is only two.

I should have explained this scenario better when I was asking the question in my earlier post.

[Rabbit]

@zmbd, it should hold even if it crosses shifts

@neopa, that would change things. If only certain people could work on certain projects, then I suppose you could take the Hungarian Algorithm and modify it for the purposes here.

Each worker and job would need to be represented multiple times broken down by hour in the matrix. Those missing a skill for a job could be assigned a high cost so they don't get assigned that job. If the jobs are more complicated than one worker per job, further adjustments to the algorithm are probably needed.

You would also need to dynamically adjust the worker's "costs" as they get assigned so they don't get an overlapping shift but also, non-overlapping jobs for that day should get cheaper for that worker so they can be assigned other jobs for that day. Correctly doing this cost update would be crucial to solving the problem.

Anyways, it's a tricky optimization problem. The Hungarian Algorithm is one method of solving the problem. But it was meant to solve a simplified version: one worker one job. Many modifications to it would be needed to get it working for this particular scenario. Details of the base implementation in the article below.

https://bytes.com/topic/access/insig...rian-algorithm