Schedule tasks in a simple workflow module of a database

Posted on 2014-08-17
Medium Priority
Last Modified: 2014-08-18
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.
Question by:Fritz Paul
  • 3
  • 2
LVL 58
ID: 40266355
<< 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.


Author Comment

by:Fritz Paul
ID: 40266446
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.
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40266520

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".

LVL 58
ID: 40266531
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.


Author Closing Comment

by:Fritz Paul
ID: 40267092
Thanks a lot Jim. Very helpful comments.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question