Novice wondering if Access is right for this project (1 Viewer)

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
I haven't used Access in years and my logic abilities aren't what they used to be. I've volunteered to take over a scheduling job at my church, thinking I could easily make a db to handle it. But there are so many elements and exceptions, I'm not sure that it's within my abilities to create a system that works. I would greatly appreciate it if someone could look at the scenario and tell me "Easy peasy" or "Forget about it."

Here's the situation:

Each weekend there are 3 church services, each service has 3 duties that need to be scheduled. Two of the duties are done by 2 people; the other just one.

I have a list of people for each duty. Some people volunteer at 2 of the duties, but I am only to assign them to 1 duty per service. (So one person is not to do 2 things per service.) That means 5 different people at each service.

Here are the twists (other than the one above): not available, wants a specific service (so not random), husband and wife team who want to serve at the same service at the same duty, husband and wife team who want to serve together--but at different duties. Then of course new people get added, some drop out.

I've made an Excel spreadsheet with a page for each duty. My biggest difficulty is with those who have 2 duties: I'll forget and schedule them for 2 duties at the same service and then have to go back and readjust.

The other difficulty is at the most popular duty at the most popular service: trying to keep it fair while dealing with a lot of absences. Hard to keep track, even visually on Excel. This one duty can take me over an hour while the rest of the scheduling is fairly quick once I log in all the absences.

Appreciate any help/advice!
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:44
Joined
Feb 28, 2001
Messages
16,820
This is not a trivial problem due to the variable number of "eligible" people to be placed and the need to "vary it up" a bit so that you don't always schedule the same people to the same duties each week (fairness issue). Not to mention the "specials" like the husband/wife team or the non-random service selection.

Can this be done with Access? Probably. Is it trivial? No. The special cases will eat your lunch on this problem. Without the specials, it wouldn't be so bad. With them? I wouldn't touch it with a 10 foot pole. BUT... perhaps someone else has specific experience in handling this kind of problem that I don't have, so don't give up on the forum just yet.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:44
Joined
Sep 12, 2006
Messages
13,892
I think you have to decide whether the "special requests" should all be accepted. I appreciate you have a lot of volunteers, but if you have helpers who only want to do flowers, or only want to help with tea and coffee after the service, you need to decide whether you can accede to all these requests.

I presume you could do this randomly in some way, but once you start to fit in with the special requests, I think you are going to have to place the "specials" first by hand, and then fill in all the remaining jobs randomly from the remaining volunteers.

You can easily do this. Simply arrange a sequence of jobs and services

Service 1, Job A
Service 1, Job B
Service 1, Job C
Service 1, Job D

Service 2, Job A
Service 2, Job B
Service 2, Job C
Service 2, Job D

then for each of these, generate a random number, and allocate the appropriate individual corresponding to the random number to the job.

It might be as easy to do this with excel. All you need is the random number generator.
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
Thanks Doc and Dave: you have confirmed my suspicions about the exceptions making it not worth the attempt to do this in Access.

Unfortunately, I have no authority, nor any pull suggestion-wise, to change the system. I DID suggest we be more strict as a local children's hospital is with volunteers, but the response was that perhaps I'd like to volunteer elsewhere. And this knowing that they have no one who could or would want to do this job.

Ah well. Guess it will be my good deed for the month--if I can keep from swearing while I do it. :D

Thanks again!
 

Micron

AWF VIP
Local time
Today, 05:44
Joined
Oct 20, 2018
Messages
2,764
Consider a calendar based solution. If not just a paper one, then Outlook calendar or Outlook meeting scheduler. Either of those could show you who is scheduled and prevent double duty I think (albeit by visual inspection). I imagine it would also make a decent print out if you're posting the plan somewhere. Then there is a ready made Access scheduler db floating around somewhere. I've seen it, it's very impressive and the creator has made it readily available. Can't recall exactly where I saw it, but can say I'm pretty sure it was here or in Access Forums.net (hope it's OK to say that here).
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
Ooh--I was searching for a ready-made scheduler that I could mess around with, but the only ones I found were for purchase by small businesses. I'll keep looking. Thanks for the tip.

I'll also check out the scheduler. There are too many people involved for me to use a paper calendar. (That is, not for the end result, but to make notes of who isn't available.)

Talking this out with my husband last night, I remembered that I volunteered for this job precisely because I had been imagining how enjoyable it would be to create an Access db to make a nice smooth process of it. But it wasn't until after I volunteered that I found out about all the exceptions. :banghead:

But to be philosophical about it: if the church made stricter rules about volunteering, people wouldn't volunteer. So I guess we just have to roll with it.

Thanks again!
 

Mark_

Longboard on the internet
Local time
Today, 02:44
Joined
Sep 12, 2017
Messages
2,112
I've had to do something similar, but the biggest headache to me would be the "Absentee" issue. How are you currently handling it when someone doesn't show up?

otherwise you are really looking at 1 service with 5 positions each time, though you will be grouping they by day to make sure you don't schedule someone for two different days when they volunteer for "Two services".
 

Mark_

Longboard on the internet
Local time
Today, 02:44
Joined
Sep 12, 2017
Messages
2,112
Special note for your "Exceptions"; Don't look to schedule "People", look to schedule "Groups". A group MAY have only one person (and most will) but could have up to 5, each of whom does one position.

For a group, you'd have what you want to call them, their availability, and their position(s) and number of members. People would have the same, but must all have the same availability for them to be in a group. Still not easy, but this would allow you to then allocate groups by service and fill in the positions from group membership.
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
What I have done is list everyone--let's say all the sacristans--on one Excel page, with a blank row dividing them by service. Dates are the column headings. I use black to fill the cell when they are not available. When all the absences are filled in, I then look at the previous month or 2 to figure out whose turn it is. I use green fill to show them as scheduled.

Not too bad except, as mentioned, all the exceptions and the one service with a lot of volunteers who all want their turn but have a lot of absences. (Actually, all services/duties have a lot of absences.)

Have to figure out how to link the cells of people who volunteer for 2 duties at the same service to alert me that I already have them down for 1 job. I know I would have to enter data rather than just color the cell. (I haven't used Excel in that way in a long time. Used to be pretty good with it.)
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
Mark, don't really understand about groups. There are only 2 couples that do the same job at the same time--and I already have 1 of them listed as "The Smiths." Everyone else does something different. So Mrs. Jones might do Job 1 and Mr. Jones does Job 2, but they want to be scheduled at the same service.

Of course Mr. Jones could be sick or on a business trip and can't make it, but Mrs. Jones can--which is the case with the second couple who do the same job. So I would schedule Ms. Johnson to fill in for Mr. Jones.

Have to go for now. Appreciate your suggestions--when I understand them. ;-)

P.S. Everyone volunteers for one specific service, so there's no accidentally scheduling someone for 2 services. What happens is that someone will volunteer to handle 2 duties at the same service. Since I devote one Excel worksheet to each duty, there's where I can make a mistake and schedule them for 2 duties at one service.
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
Thanks! I joined UtterAccess so I could download. Will spend some quality time studying this to see if it's usable for my purposes.
 

Micron

AWF VIP
Local time
Today, 05:44
Joined
Oct 20, 2018
Messages
2,764
Hmmm. I'm not a member and got it. Maybe they changed policy.
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
Oh boy, I just found a program that does everything our church could possibly want and then some, "Ministry Scheduler Pro." They mention every problem I deal with currently--double scheduling and even keeping families together.

I will present it to the secretary on Monday to see if they are interested and could afford it. I told my husband I'd like to offer to help with the costs so that they have to let me be in charge, because it looks like it would be so satisfying compared to my spreadsheets.

What they ought to love is that every volunteer pretty much takes ownership of their own schedule: they log into the program to submit their availability and preferences--and after the schedule is generated, they will get reminders when it's their turn and the option to get a substitute.

And I've only been given a portion of the scheduling; this program could handle all of it.

I'm in love--but I know not to get my hopes up too much. What seems sensible to me rarely strikes others the same way.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:44
Joined
Feb 28, 2001
Messages
16,820
A bit of advice: In terms of the decision to make or buy: How long can your "client" wait for the build and how much would a purchase cost? Since you were volunteering, the cost to build is not monetary, but there is a cost of some kind associated with not having something when you needed it. If the price isn't too bad and the shipping delay isn't too bad, you would do well to look into the purchase in depth.

Just watch out for things like per-user license costs, necessary ancillary software needed to support the scheduler package, and other hidden "gotcha" costs. You can be in love and enthusiastic - but don't be blind.
 

Bham45

Registered User.
Local time
Today, 02:44
Joined
Jul 17, 2014
Messages
87
Of course I'm looking at the price of the entire package--and so would the parish. Don't know what you mean about waiting for a build. I've already determined I'm not going to attempt creating a db.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:44
Joined
Feb 28, 2001
Messages
16,820
"Waiting for a build" in the world where I worked before I retired had the connotation of waiting for the developer to put together a usable package with as many bugs as possible having been removed. But there is a "build cycle" of refining and releasing new versions on some schedule. The problem of "waiting for a build" is that there is a cost in regular industry for not having productivity software ready to be productive. So normally there is a monetary cost (in lost productivity) while waiting for the productivity tool to be ready. That is what I was implying about costs while waiting for something to be ready.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom