Shifts schedule database

[Bilakos93]

Hello everyone

Haven't quite got my head around it yet but I thought I'd post a question before I start making something that may not be possible

I wonder if there could be a database that would manage a (monthly) shift schedule for employees (doctors in a hospital) that would also take into account things like the following:

On call schedule


- 12hour on call shifts every 4 days
- 24hour on call shifts for the rest of the month
- annual (and any other) leave
- people's preferences as to when not to be on call
- the shifts have to be evenly divided (as much as possible)

Daily work (9-5) schedule for various posts
- annual (and any other) leave
- zero days (meaning that people can't work after an on call shift - with some exceptions)
- e.g. post1 will be covered by employee2 on 1st July, employee3 on 2nd July etc. There will be around 10 posts.

The database will produce a monthly on call schedule and a monthly daily work schedule.
- The on call schedule will be a table with the dates of the month and the names of the on call staff
- The daily work schedule will be a table with the dates (rows) and the posts (columns) and then the initials (or something like that) of the person covering each post each day in the table

This is a rough design I have in my mind
I suspect there will be some (maybe heavy) vba involved in it

Do you think it's feasible?
Any thoughts or advice?

Thank you

[Welshgasman]

Feasible, but difficult to implement, even for an experienced developer (unless they had already created one.)
I would be looking to buy a bespoke package.

[CJ_London]

What does

- the shifts have to be evenly divided (as much as possible)

Mean?

[Bilakos93]

The shifts will have to be evenly distributed amongst the employees
But this is something the user will have to do I suppose

[Welshgasman]

The shifts will have to be evenly distributed amongst the employees
But this is something the user will have to do I suppose

Well the system would be capable of showing how many each employee has.

[CJ_London]

And is every shift constructed with the same requirements in terms of skill sets/experience/etc? Whether it be day/night/weekend?

[Bilakos93]

And is every shift constructed with the same requirements in terms of skill sets/experience/etc? Whether it be day/night/weekend?

No, there are some requirements. I think on the table employees there will be date fields for each on call post.
e.g. employee1 will be able to cover post2 from July 15th 2024 onwards. Employee2 will be able to cover post3 from August 10th 2024 onwards.
Or alternatively there could be a yes/no box (e.g. employee1 meets - or doesn't - the requirements to cover post3) but this won't make sense in looking at the data retrospectively

[mike60smart]

How do you currently manage this process?

[Bilakos93]

my understanding is that they use lots of excel spreadsheets and trying to do everything manually.
They have a spreadsheet that includes leave and people's preferences (meaning when they don't want to be on call) and then they create a second one (for each month) trying to allocate the shifts manually.
Similarly for the rest