Schedule tasks in a simple workflow module of a database

I am building a database for a small property management company.

They have 6 people in the company and have around 200 residential units on their books.

All properties have fixed schedules for cleaning, garden maintenance, routine inspections and invoicing.

They want to schedule these activities in an Outlook type way. For instance for a specific property, they want to be able to schedule weekly gardening service like:

Activity: Gardening Service
Frequency: Two weekly
On Which days: Tuesdays
Up to when: Until terminated.
Responsible Person: George Johnson
Cleaning Company: Flash Cleaners

Out of this they want to extract a list of People responsible, Properties, and Tasks for any given nearby day.

How should I approach this? Especially the continuance "Until terminated". I suppose one can just fill a table with dates for Tuesdays for 10 years. Then after 10 years reminders just suddenly stops. Is that the right way? Do you know if that is the way that Outlook does it? Can and should I use Outlook for this process?

Are there any ready Access templates for this process that you know of?

I appreciate inputs.
Fritz PaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< I suppose one can just fill a table with dates for Tuesdays for 10 years. Then after 10 years reminders just suddenly stops. Is that the right way? >>

  No, not really a good way.  I'm not sure how Outlook does it, but I would lay it out just like you have it.   Then have a procedure or series of procedure that scans the tasks and decides what is a valid task or not.

 For example, a procedure might accept a start/end date/time, scan the tasks, build up an array or table of the tasks, and return.

 Like wise, you might pass filters for person, or company.

  There are a large number of ways this could be structured, but pre-filling a calendar I don't think is a good approach.

Fritz PaulAuthor Commented:
Thanks Jim.

That makes sense if I understand it correctly.

So if a task is scheduled for every Monday and we filter for a date which falls on Monday, then the code finds if the date is a Monday and then looks for all tasks which is scheduled for Mondays, and from the results one can filter further for the specific property unit or responsible person.

In the case of a two weekly on a Monday activity. Do you agree that first the code determines if the date is a on a Monday and then whether the activity was carried out one or two weeks ago. However what I expect is that it will sometimes happen that some activity may be changed to another date, due to weather or public holiday/s.

What do you suggest for this multi period situation and variation of specific dates.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

As I said, there are a bunch of different ways of structuring this.   When scheduling, there are a few approaches on task frequency.  To give you a sense of that, here's a screen shot of a task setup for a task scheduler I wrote in Access:

Task settings
  As you can see, there is a "run once" option box, an active from/to, and then check boxes for either allowing either specific dates, days, day of the month, hours,  or a repeat interval.

  Here the situation was a bit different though.  What I did is every time a task would run, I would calculate a "next run" date/time.   The scheduler wakes up each minute, checks for tasks on that day/minute, and if found runs them.   It then calculates the next run time.

 You could do the same thing; run your procedure/report for each day, advancing day by day.   That however doesn't allow you to jump into the future and look two months down the road and see what tasks are scheduled.

So in your case, I would set it up like this and start off simple:

1. Allow for running a procedure/report for a specific date.

2. For tasks, allow for
a. A start/end date range for when the task is active.
b. Include a "Repeat x times" - This covers doing a task once, a number of times, or infinitely (0)
c. A start date and a repeat every x days.
d. Add a day of the week text box.

and the logic:

1. Is the "run date" currently in this tasks start/end date range?  Y or n.  If N, stop
2. Calculate a first run date and the repeat interval.   For C, you have it.  For D, you'll need to calculate it off the start date range.
3. Calculate the diff in days between the "run date" and the first run date, divide by repeat interval.   If it's not a whole number, then today's not the day.
4. If today is the day, is the result of 3 > b?  if Y then stop.  If no, then the task is due for this "run date".


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Once you have that, then I would try adding the specific day of the month and months options.

And just food for thought; start date for A and C might be one in the same.   Like wise you could use the same date for start and end date to indicate that the task is only to be done once or a blank end date means that it is to repeat indefinitely.

As I said, lot's of ways to structure this.

Fritz PaulAuthor Commented:
Thanks a lot Jim. Very helpful comments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.