Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2019, 09:11 AM   #1
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Novice wondering if Access is right for this project

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!

Bham45 is offline   Reply With Quote
Old 05-16-2019, 12:16 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,929
Thanks: 79
Thanked 1,562 Times in 1,450 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Novice wondering if Access is right for this project

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Bham45 (05-16-2019)
Old 05-16-2019, 12:31 PM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,763
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Novice wondering if Access is right for this project

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.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
Bham45 (05-16-2019)
Old 05-16-2019, 02:43 PM   #4
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Re: Novice wondering if Access is right for this project

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.

Thanks again!
Bham45 is offline   Reply With Quote
Old 05-16-2019, 03:44 PM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 703
Thanks: 3
Thanked 145 Times in 139 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Novice wondering if Access is right for this project

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).
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Bham45 (05-17-2019)
Old 05-17-2019, 09:50 AM   #6
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Re: Novice wondering if Access is right for this project

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.

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!
Bham45 is offline   Reply With Quote
Old 05-17-2019, 09:51 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,858
Thanks: 17
Thanked 351 Times in 348 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Novice wondering if Access is right for this project

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_ is offline   Reply With Quote
Old 05-17-2019, 09:59 AM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,858
Thanks: 17
Thanked 351 Times in 348 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Novice wondering if Access is right for this project

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.
Mark_ is offline   Reply With Quote
Old 05-17-2019, 10:15 AM   #9
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Re: Novice wondering if Access is right for this project

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 is offline   Reply With Quote
Old 05-17-2019, 10:25 AM   #10
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Re: Novice wondering if Access is right for this project

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 is offline   Reply With Quote
Old 05-17-2019, 10:55 AM   #11
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 703
Thanks: 3
Thanked 145 Times in 139 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Novice wondering if Access is right for this project

I did download a copy, and to my surprise was smart enough to put a txt doc with the source path in the folder:
http://www.utteraccess.com/forum/Ms-...-t1969978.html
Maybe not what you need, but worth a look I'd say.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Bham45 (05-17-2019)
Old 05-17-2019, 11:33 AM   #12
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Re: Novice wondering if Access is right for this project

Thanks! I joined UtterAccess so I could download. Will spend some quality time studying this to see if it's usable for my purposes.
Bham45 is offline   Reply With Quote
Old 05-17-2019, 12:21 PM   #13
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 703
Thanks: 3
Thanked 145 Times in 139 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Novice wondering if Access is right for this project

Hmmm. I'm not a member and got it. Maybe they changed policy.
Micron is offline   Reply With Quote
Old 05-17-2019, 02:32 PM   #14
Bham45
Newly Registered User
 
Join Date: Jul 2014
Posts: 87
Thanks: 8
Thanked 0 Times in 0 Posts
Bham45 is on a distinguished road
Re: Novice wondering if Access is right for this project

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.
Bham45 is offline   Reply With Quote
Old 05-17-2019, 07:26 PM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,929
Thanks: 79
Thanked 1,562 Times in 1,450 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Novice wondering if Access is right for this project

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wondering if my Access 2010 is buggered Design by Sue Forms 3 10-03-2012 09:43 AM
Access 2003: Create Project and add Records to Project spudracer Queries 7 01-20-2011 01:17 PM
Access 2003 Project (ADP) vs Access 2007 Project ions General 32 03-16-2010 12:00 PM
Wondering if Access DB is the way forward kelliec General 13 09-11-2009 12:46 AM
Was wondering if we could change the amount of connections to access Luckydvl General 5 10-07-2004 09:28 AM




All times are GMT -8. The time now is 11:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World