Access Db for Employee Scheduling: Need ideas for erratic schedules

[ccheaton]

Hey! I'm working on an Access database that will function to automatically generate a work schedule for 180 people in the operating room that I work in. I've got a good amount of Access experience, though I'm not overly proficient in VB.

Here is the situation: MOST of the employees work a fixed schedule, like Monday-Friday, 7am to 3.30pm. I can put that together without a problem.

However, some employees work quite erratic schedules, like Tuesday from 7am to 11am, Friday from 3pm to 7pm, and every third saturday from 7am to 3.30pm. There are 10-15 people with these erratic schedules. I don't want to code in specific exceptions for these employees, because the turnover here is pretty high, and there's always somebody new coming on board with an erratic schedule.

My problem is that I cannot envision how to structure the tables, relationships, etc... to accomodate both the regulars and the erratics. Especially those that work every third Saturday or something.

For the regulars, I am generating a default schedule for them based on their regular shifts, and then adjusting for the days that they request off. In the employees table, I have linked fields to shift and 'days' tables where I can enter their regular schedule. Exceptions are then stored in another table. The end result of all of this is that it is output to Excel.

How would you suggest that I enter the 'regular erratic' schedules? Any thoughts would be appreciated. I'm experiencing a mental block about it right now.

Thanks
Clay

 

[MichaelRed]

I would have a seperate table for the erratic ones. Another "object" would include the rules for these erratic individuals, and one or more functions would generate their "forecast" availability for the schedualing period (of interest). To generate a shcheual, you would do a query on the 'static' individuals, run the function(s) to generate and availability thable/chart for the erratic ones and a query aginst this to add them to the current schedual.

It is my understanding that most hospital employees have some variability in their work scheduals, so it might be easier to (conceptually) design the entire setup as "erratic" individuals, generating a recordset on each schedual generation giving each employee specific date and time availability for the period and using this recordset as the basis of the schedual. At least in concept, it would appear to make the system a bit more flexible and have better control over the exception rules for the availabiliy of individuals for the schedual. I would also envision a table of "rules" for the repeating occurances (every Monday; Evewry Wednesday; Every third Saturday; ...) and a seperate table of exception rules (not between #11/19/2003# and #11/24/2003#; Never on a Sunday; ...) Some standardization of the rules tables nomencalture would be necessary, and a procedure developed to use the nomenclature to parse the records and determine absoloute dates within the schedual period. The rules would, of course, be used to generat the base availability and then to remove any entries in the exception group.

I realize this is quite general, and that setting this up will require some time and a bit of refinement in addition to the simple fleshing out of the generalities to the details, but I, at least, see no simplistic soloution.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[vandaliarental]

I'm looking to also do a scheduling db with erratic schedules & just wondered what you ended up with?

I'm having problems laying out the forms to enter the information. What I envision is the employees listed down & the dates listed across the top, then fill in scheduled times in cells. But I'm not sure the best way to generate the dates across the top.

Thanks,
Connie
Vandalia Rental

 

[GingerR]

Hi Connie. The way you describe your 'vision' sounds like an Excel-type layout. Generally data in Access isn't entered in this way. Are you planning on entering time into each and every "cell"? I would try doing something like the Windows Scheduled Tasks utility (go START+Programs+Accessories+Systems Tools + Scheduled Tasks). This just lets you set up something to run on your computer automatically, but what I wanted you to see was how it's set up. Not necessarily how the forms are, but the logic (Weekly, Daily, One Time Only, Every Third Saturday, etc). So fire it up, hit Add Scheduled Task, pick an application (doesn't matter which cause you can just delete it later), and take a look at the little schedule wizard and how the choices change depending on if you pick Daily, Weekly, etc. I'd think about setting something like this up...It will make your db very flexible so adding a new person's schedule or altering an existing schedule won't be painful. If you can think through how to separate the options your employees have into the basic common chunks of their work schedule options, I'm guessing you'll end up with something similar to the Scheduler.

So I'd write down all of those things and figure out how to design the tables to hold the info. Once the form is made, and the data is captured, you can develop reports that take the entered data and blow it out over real dates and fill up a schedule page. And set it up so one person can have multiple pieces of schedules too. So if someone works M-Th 7:00 am - 3:30 pm, this ends up in the table:

Unit Day Start Finish StartDate
Daily M 7:00 am 3:30 pm 1/1/04
Daily Tu 7:00 am 3:30 pm 1/1/04
Daily W 7:00 am 3:30 pm 1/1/04
Daily Th 7:00 am 3:30 pm 1/1/04

Then say they work every third saturday, you'd have to have an additional field for any Weekly schedules to hold "Every X Weeks":

Weekly Sa 4:00 pm 11:30 pm 1/1/04 3

Then say the fourth Sunday every month they come in for a special thing, you'd have to have fields to hold which week and which day of the month:

Monthly Su 8:00 am 12:00 pm 1/1/04 4 Sun

And so on, thinking through which bits of data you'd need to capture for each type of schedule bit, making those fields in the table, and getting the data into the table based on the picks the user makes on the form.

As you can see, later say this person goes on to a different schedule. If you add a "END DATE" field to the table, you can end one bit of their schedule and add another.

So hopefully this makes some sense to you. It could be a little daunting I think, but if you just start thinking though the time-chunks it's doable. Basically if you set up the form to be a seemingly simple interface for the user and as they pick items, it fills the table with the info you need. Then later you use this info in calculations to fill up a calendar-type report.

Does this make sense?

 

[GingerR]

Hi again...If this is too much for you, I just went here:

http://www.sharewareconnection.com/search.php

searched on "Work Schedules" and got lots of hits. Seems there are a ton of little freeware/shareware programs available. Maybe one of them will work for you.