Complex Staff and Weekly Rota Database Structure (1 Viewer)

Hello All.

I've had a pop at a few databases before, and always regretted not getting the basic structure correct right at the beginning, so I was hoping for some pointers here!

Basically, I'm responsible for generating an Operating Theatre staffing rota for a small NHS Hospital. There are 10 theatres, each needing a surgeon, a consultant anaesthetist, and often a trainee anaesthetist. I've attached a pic of the end result of the weekly rota - at the moment I do it in excel.
SOme of the problems I have at the moment are:

1. My boss allowing too many people off on a given day - there are supposed to be limits to the number of each group of staff off at any one time, but it can be difficult to track
2. Using people in the rota that are in fact off - either because there absence hasn't been recorded in the paper diary, or simple human error when generating the rota
3. Using the same person twice - for eg the same anaesthetist in theatre 1 and theatre 5 on monday

I have to juggle around 30 staff members a day, and it's easy to miss things. At the moment I use conditional formatting to flag up duplicates in excel, but it it's not perfect - eg. spell someone's name incorrectly and it doesn't work.

I also need to be able to find out for example, how many vascular surgery lists a trainee has worked on.

Past rotas need to be easily accessible - for eg one of our managers recently asked for a copy of the last 6 months rotas in order to put together a case for extra staff. It would be nice to be able to get this with a single click!

I think the basic staffing database is pretty well covered in many database howto's, so I'm not too worried about that. It's the actual weekly rota that I need to get to grips with.

So, my questions are:

1. Is it feasible to generate a rota grid like the one in the pic with access? Could I get the report generator to build something like it, or should I do it with a form that looks reasonable when printed?
2. I don't really understand working with dates in access - will I need a script to fill a table with a year's dates that I could then link to theatres and staff etc? Is there an easier way to do it.
3. How should I organise the data in my tables? Week-->7 days-->10 theatres-->three staff each?

The other little fly in the ointment is that the rota template for each week is not the same - it works on a four week rolling rota, so th1 does something different in w1, w2, w3, w4, then goes back to w1!

Any advice would be much appreciated!

If it works, use it

>So, my questions are:
>.s it feasible to generate a rota grid like the one in the pic with access? >Could I get the report generator to build something like it, or should I do it >with a form that looks reasonable when printed?

I had a contract last year to produce something very similar to what you describe for a government agency.

Due to security restrictions I was not able to use some Active X tools like data grids which is what I would recommend trying to use.

Instead I came up with a solution that combined a flat file table to store the data which could be populated every time it had to be viewed, by a VBA routine then deleted prior to the next viewing.

I used the table as a basis for a form with buttons that controlled the VBA to refresh the form with the week after's rota or week previous to the one being viewed. This allowed a full week for 200 people to be easily viewable on screen. The actual data used to populate the flat file table was in a normalised data structure which made other aspects of designing the system as easy as one could expect.


>I don't really understand working with dates in access - will I need a script >to fill a table with a year's dates that I could then link to theatres and staff >etc? Is there an easier way to do it.

If you design with a normalised structure, I would recommend that every shift be a unique entity with a ref to the employee, start date of shift, end date of shift, start time of shift and end time of shift. plus whatever else is appropriate for that shift (type of shift etc.) I was able to capture user clicks on the form so that a new form was displayed to allow shifts to be changed.

>How should I organise the data in my tables? Week-->7 days-->10 theatres-->three staff each?

Like I say, normalise everything but then plan to display it in an unnormalised way. You will need to code the system to enable this but if you can make the leap of thinking to separate the view from the stored data that just becomes a matter of working through the logic.

Cheers,

Mark Smith

bernmc said:

Hello All.

I've had a pop at a few databases before, and always regretted not getting the basic structure correct right at the beginning, so I was hoping for some pointers here!

Basically, I'm responsible for generating an Operating Theatre staffing rota for a small NHS Hospital. There are 10 theatres, each needing a surgeon, a consultant anaesthetist, and often a trainee anaesthetist. I've attached a pic of the end result of the weekly rota - at the moment I do it in excel.
SOme of the problems I have at the moment are:

1. My boss allowing too many people off on a given day - there are supposed to be limits to the number of each group of staff off at any one time, but it can be difficult to track
2. Using people in the rota that are in fact off - either because there absence hasn't been recorded in the paper diary, or simple human error when generating the rota
3. Using the same person twice - for eg the same anaesthetist in theatre 1 and theatre 5 on monday

I have to juggle around 30 staff members a day, and it's easy to miss things. At the moment I use conditional formatting to flag up duplicates in excel, but it it's not perfect - eg. spell someone's name incorrectly and it doesn't work.

I also need to be able to find out for example, how many vascular surgery lists a trainee has worked on.

Past rotas need to be easily accessible - for eg one of our managers recently asked for a copy of the last 6 months rotas in order to put together a case for extra staff. It would be nice to be able to get this with a single click!

I think the basic staffing database is pretty well covered in many database howto's, so I'm not too worried about that. It's the actual weekly rota that I need to get to grips with.

So, my questions are:

1. Is it feasible to generate a rota grid like the one in the pic with access? Could I get the report generator to build something like it, or should I do it with a form that looks reasonable when printed?
2. I don't really understand working with dates in access - will I need a script to fill a table with a year's dates that I could then link to theatres and staff etc? Is there an easier way to do it.
3. How should I organise the data in my tables? Week-->7 days-->10 theatres-->three staff each?

The other little fly in the ointment is that the rota template for each week is not the same - it works on a four week rolling rota, so th1 does something different in w1, w2, w3, w4, then goes back to w1!

Any advice would be much appreciated!

Click to expand...

Hi

I'm attaching a sample database that may be of some help to you.

Best regards

Maurice St.Cyr

Thank you both - very helpful. (I had to look up 'normalised data'!!!)