James Gifford said:
I'm designing a multi-person scheduling tool in Access 2000, and I'm
not quite sure what form to use for the basic data.
Okay: here's a more complete description of the problem, the needs and my
outline for a solution. (Warning: Long and boring except to gurus who
like this kind of problem-solving.)
SITUATION: A company with a large field staff providing daylong services
in clients' homes on a recurring and long-term basis. There are presently
about 65 staff for about 40 clients. Clients are seen 3-5 days of each
week, for 4-8 hours per day, for a period of a year to three years. The
staff and client lists are relatively stable and change only slowly.
However, there are many factors driving each staffer and client's
schedule and available times and needs shift frequently. Our staff has to
coordinate multi-person meetings related to each client on weekly,
monthly and semi-annual schedules, and work around appointments with
other service providers not under our control.
Scheduling is a *major* headache and lots of time is being lost to
inefficiency, long drive times, and miscommunication.
At present, the dozen senior staffers spend the first few days of each
month creating a schedule that meets all the known needs, using pen,
paper, Outlook, Palm Desktop, luck, guesses and much email and voice
communication. (I should add that nearly all of the staff operates
independently and there is very little central office time for most -
some never set foot in the corporate office and the rest only meet there
infrequently. This company's lifeblood runs on email, voicemail and cel
phones.)
Unknown factors and changes mean that schedules rarely survive very long,
and scheduling changes and crises occur many times a week. The company
has hit a severe growth wall because of the difficulty in expanding
competent staff and the efficiency losses.
Most of the staff is also heavily booked - this is not a matter of a few
appointments a day, but as many as five to seven each and every weekday,
each with its own scheduling problems.
Complicating the matter is that each staffer *must* meet contractual time
obligations to each client, so if an appointment is canceled or missed
(which happens frequently for a variety of reasons), the time must be
rescheduled, as-soon-as and within the current calendar month if
possible.
The number of staff and clients could easily double within the lifespan
of this app, so that needs to be considered in any immediate solution.
Once that threshold is reached, there would be resources for a wholesale
update of the app if necessary.
SOLUTION: Install a Scheduling Manager who absorbs responsibility for
maintaining a database of people and elements related to scheduling,
creates initial schedules for all staff and clients on a monthly,
biweekly and/or weekly basis, and acts as a single-point contact to
manage schedule changes. This may evolve to a two- or three-person
workgroup but will NEVER involve schedule participants.
PROBLEM: Giving this Scheduling Manager adequate software tools to
efficiently execute the task of creating, maintaining and updating
interlocking scheduling involving some 250-300 individuals.
There are many scheduling tools available, but if any match the model of
"one person managing a multitude of recurring schedules for a fixed and
long-term group of individuals," I haven't been able to find it. Project-
oriented tools like MS Project won't do it. CRM-type tools like GoldMine
and ACT won't do it. Individual schedulers like Outlook and Palm won't do
it. And the groupware schedulers like those used by many universities,
where each participant has a web portal to enter their own schedule and
retrieve coordinated scheduling information, won't do it. (The last might
have worked a few years ago, but the need to put the scheduling in the
hands of one person who is removed from the schedule is driven by many
factors.) I also looked into facilities and resource management tools,
but none is flexible enough to be adapted to this kind of overlapping,
interlocking scheduling.
So unless someone can point me to a powerful, single-user tool that does
this job - and I'll pay a bounty to anyone who points me to an off-the-
shelf solution! - a custom app is needed.
GOALS & IMPLEMENTATION: A useful starting point with value to other areas
of the company is a comprehensive database of staff, clients and third
parties the company interacts with. This is a relatively straightforward
application of Access and is in progress. Extending this database with
scheduling information - a rule-base and an availability chart - for each
individual is also relatively straightforward.
Getting from this stage to useful schedules for each entity is where I'm
stuck. Most of the scheduling app models I've looked at are too simple to
efficiently handle the complexities of this application.
The FIRST-LEVEL goal is to create a tool that will let the Scheduling
Manager build schedules in weekly blocks in an Outlook- or Palm-like
interface, manually defining appointments one at a time and having the
information reflected in each participant's schedule. Minimal AI to
highlight conflicts and assist the process would be nice. Expectation is
that the SM can put together a month's draft schedule for all parties in
2-3 workdays.
The SECOND-LEVEL goal would be to have enough automation to let the app
use the rule-bases and availability schedules to create an initial draft
schedule, with problems highlighted, for the SM to correct and fine-tune.
This stage should also provide tools to let the SM enter schedule changes
and corrections with "what-if" feedback showing the effects on other
schedules.
The THIRD-LEVEL goal would be to automate schedule creation to the point
where the first draft is "perfect" according to the rules and no manual
fixup would be needed. This stage should also be able to reschedule
cancelled and missed appointments as automatically as possible.
A long-term and secondary goal would be to keep the app flexibly designed
so that it could easily be converted into a standalone, configurable, and
marketable tool. (Two design decisions already made with this in mind are
24-hour scheduling capability, even though there is no foreseeable use
for scheduling outside the 8am-7pm range; and 10 or 15 minute scheduling
blocks, even though 30-minute increments are more than enough granularity
for foreseeable needs.)
RESOURCES: Effectively unlimited. Workstation horsepower, disk space,
network space, etc. are only limited by current SOTA. Budget is "ample."
Timing is "as soon as" but six months to a year to develop the system to
the semi-automated point is not excessive.
That's pretty much the whole shape of the problem: all comments,
suggestions and ideas welcome. If there is a data model for the
individual schedules that does not involve an array of all the time
blocks (and perhaps two: one as an availability and rules mask, one as
the actual current schedule), that would permit rapid comparison and
what-if updating, I would very much like to know. It does not seem to me
that having the app walk through a dozen linked lists for each inquiry
will be efficient and robust.
Many thanks in advance for taking the time to read through this and
comment!
