StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POScheduling Database (Base Schedule + Exceptions)
text
Body
copied!<p>Currently our employee scheduling for ~800 employees at a 24/7 company is handled with Excel workbooks. I need to move all of the scheduling into an Access employee database I designed that we've been using for years. (NOTE: I know Access isn't the ideal platform for this but it's what I have.)</p> <p>Each employee has a base schedule such as 2:00am start times with Wed/Thu off. An employee's schedule for any given week will be their base schedule modified by exceptions such as:</p> <ul> <li>Time-Off requests </li> <li>Shift switching with another employee for a day</li> <li>Leaves of Absence (basically another form of time off) </li> <li>Schedule changes based on company needs made by a scheduling administrator</li> </ul> <p>The database needs only store the base schedule and somehow display a given week's schedule. It <strong>doesn't</strong> need any advanced logic like scheduling based on availability.</p> <p>I can see a few ways to implement this. The first one that came to my mind was storing the base schedule and then dynamically generating a given week's schedule as needed by combining the base schedule with tables based on the above exceptions (time-off, switches, etc). However, I can't see how to store the base schedule and how to merge the base with the exceptions to generate a schedule. I would think a table like baseSchedule(PKScheduleID, FKEmployeeID, DayOfWeek, StartTime) but I'm not sure.</p> <p>Another method would be to generate weekly schedules into a table, for example using the "three table Kimball Star" method described here: <a href="http://www.damirsystems.com/?p=466" rel="nofollow noreferrer">http://www.damirsystems.com/?p=466</a>. Basically it creates a table full of dates and has a many-to-many relationship with employees to define a schedule. I dislike that method for many reasons such as needing to check/modify that table at the application level every time time-off, etc is added and the need to "generate" a new schedule into the table. Also, it's possible this will swell to 2,000+ employees and I fear poor Access will explode in a ball of flame having a record for every employee for every day.</p> <p>Does anyone have any design ideas for implementing the base schedule + modifiers method? I'd love to generate schedules on the fly with queries only but I'm comfortable with using VBA if necessary.</p> <p>Thank You</p> <p><strong>Edit 8/19/11 4:30pm:</strong> I think I'm going to go with something very similar to bluefeet's answer. Below is the design I mocked up in a blank DB:</p> <p><img src="https://i.stack.imgur.com/gdgfy.jpg" alt="Design"></p> <p>Each employee will have a record in the Base table for each day of the week with a start time and the number of hours they're scheduled to work. There's also an Exceptions table listing modifications to the schedule with a date, employee, and his new shift. </p> <p>For the application level forms and reports I'll pull the <strong>base</strong> schedule into a recordset with a very sloppy query that outputs something like:</p> <pre><code>Name Mon Tue Wed Thu Fri Sat Sun Alice 6:00 PM 6:00 PM Off Off 2:00 PM 2:00 PM 2:00 PM Bob 4:00 PM 4:00 PM 4:00 PM 4:00 PM Off Off 4:00 PM </code></pre> <p>Then, in VBA, I'll also pull the exceptions for a date range (a week) into a recordset. I'll loop through the exception recordset, modifying the base recordset (from above) as I go. Then I'll set the form/report to use the modified recordset. It's a bit inelegant but it'll get the job done well enough. </p> <p>If anyone has any ideas as how to combine the Base and Exceptions tables with output similar to the above using only queries and no VBA please let me know.</p> <p>Thanks Again</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload