Help: Automated Rotating Schedule

[Breezer23]

Thanks for taking the time to read this and (hopefully) help out. I have a big Excel Guru but it's been ages since I've spent any time in Access. Recently, I was asked to create an automated scheduling tool. This tool is one that we would automatically publish for our employees to see. One of the primary goals is to create a tool that'll be completely automated, keep in mind this is a day to day schedule and a 365 day a year operation. Here are some of the demands:

• The Database would need to be able to recognize the changing days each date falls on every year (1st of February 2012 might be a Monday but it needs to know that in 2013 it'll be a Tuesday, etc).
• Schedules are generally fixed as assignments are based on the day of the week but the schedule needs to be able to flexible enough to allow for quick changes and adjustments when employees are on Paid Time Off or on leave.
• Shifts do not change but there are two locations so timezones must be accounted for.
  
  • 4:45am (MST) to 1:15pm (MST)
  • 6:30pm (EST) to 3:00pm (EST)
  • 1:30pm (MST) to 10:00pm (MST)
  • There are a few more but it's not that important to give shifts.
  
  
• There are 4 "color" groups. An employee can be assigned to ONE color group. A color group refers to the actual work this agent is allowed to do (3-4 sets of assignments within each color group)
  
  
  
  • One of the 4 color groups can be used as filler or overflow for ALL color groups
  
  
• Each assignment MUST be covered at all times (8:00am to 11:00pm Eastern Time). Assignments are generally fixed on weekdays but may change on weekends (if they do it's very slight)
• Some assignments require multiple agents from the same color to be assigned concurrently. Doubled assignments have priority levels
• Some assignments are NOT to be covered by more than one agent.
• There are 5 managers. Each week one manager needs to be listed as being on call and this rotation should change each week on a basic rotation allowing for equal shifts each year.

As you can see it's quite demanding. If someone is willing to give some basic or go above and beyond and give some detailed database design tips here that'd be great. I've been asking for assignments for ages and although they knew I wasn't that great with Access, this is what they gave me and I'd hate to be a let down. This is a learning experience for me and I'm NOT asking for someone to do this for me. I'm simply asking for some basic guidance to get this going. Unfortunately, this info itself is something I can't share as it has to do with operations for a very large company which is why I'm only giving a basic outline.

Again, thanks for taking the time to read through this. I appreciate it.

[June7]

This will be a very complicated and long-term development project. Might find something already built that meets your needs but probably at a cost, which I expect would be less then the salary cost for your time.

Google: Access scheduler database template

http://www.access-template.com/Access_Scheduler.html

[Breezer23]

This will be a very complicated and long-term development project. Might find something already built that meets your needs but probably at a cost, which I expect would be less then the salary cost for your time.

Google: Access scheduler database template

http://www.access-template.com/Access_Scheduler.html

Yeah, that is what I was thinking at this point. Unfortunately, they'd never put money towards that. Thanks for the quick response.