Microsoft Access Tips for Serious Users

Provided by Allen Browne, February 2008

Recurring events

To handle recurring events, you need a table of events and one of these approaches:

1. occurrences calculated on the fly.
2. a related table for each occurrence.
3.  occurrences calculated on the fly, and a related table for exceptions.

(A) is fully normalized - easy to implement and maintain. Change any aspect of the event, and all instances reflect the change.

This is the best solution for simple sequences, but it cannot remove specific instances from the series or reschedule them.

(B) lets you move or remove specific instances from the series. However, it cannot cope with events that have no termination date, since you must create a finite number of instances in the related table. Also, if the user alters the information in the original event, you have headaches trying to figure out which new instances to create, old instances to delete, or existing instances to reschedule when the user may have manually set them or entered comments they want.

This is the best solution for finite sequences where each instance must be handled individually, such as a schedule of repayments.

(C) combines most of the advantages of (A) and (B). This is the best solution where you occasionally need to modify the series of events.

This article takes the third approach. Download the sample database (45kb zipped, Access 2000 and later.)

Storing the event

In the sample database, tblEvent has these fields:

PeriodTypeID will be a combo offering values that can be used in a DateAdd() expression: "d", "ww", "m", "q", or "yyyy". For example, if something occurs every calendar month, you enter 1 for PeriodFreq, and "m" for PeriodTypeID. If it happens every 30 days, you enter 30 for PeriodFreq, and "d" for PeriodTypeID.

The lookup table ltPeriodType contains the values for the combo.

Calculating the occurrences

To calculate each instance of an event, use a Cartesian Product query. Create a table with a Number field, and enter the values from zero to the highest number of instances you could need. In the sample database, tblCount has 1001 records (0 to 1000.)

qryEventCartesian uses tblCount and tblEvent, with no join between them. This gives every combination of the two, so 1001 records for every event. Add criteria under tblCount.CountID like this:
    <=[tblEvent].[RecurCount]
This yields the desired number of records for each occurrence, with the count indicating which instance it is.

But the open-ended events no longer match because the RecurCount is Null. We want the entire count for these, so add this on a separate criteria line:
    Is Null

This simple query achieves option (A) above, i.e. all occurrences calculated on the fly.

Handling the exceptions

Now we need another table to handle the instances that are rescheduled to another date, or to be skipped. tblEventException has these fields:

The primary key is EventID + InstanceID, so you cannot enter two exceptions for the same instance of an event.

To reschedule a particular instance to another date, enter the event, the instance number, and the new date. To cancel a particular instance, check the box in the IsCanned field.

qryEventDates then identifies the cancelled instances, and uses the special dates for the rescheduled instances. It uses the query above as an input "table", with an outer join to tblEventException.

The expression to calculate the EventDate involves nested IIf() functions. See the sample database for details.

The report rptEvent displays the results of this query. To limit it to a particular date range, you can easily add criteria on the EventDate field, or use the WhereCondition of OpenReport.

The interface

The form is bound to tblEvent, with a subform bound to qryEventDates to display the event occurrences:

Naturally the subform is read-only: most instances do not exist in any table. So, we provide an Edit button beside each instance where the user can enter, modify, or delete the records in tblEventException. The button opens a form in dialog mode, finding the record if it exists, or setting the Default Value for EditID and InstanceID so any new record the user adds will be for the correct row. When you close the dialog, it requeries the subform.

Going further

The sample database demonstrates the principles. Develop it further if you need to associate individuals with events and/or specific occurrences.

The recurrences are very flexible, but do not handle things like "the 4th Tuesday of each month."

You will need more reports, showing events in daily/weekly/monthly/annual layouts. MVP Duane Hookom has some examples of Calender Reports.

For the curious

Question 1. Why did you use a Number field instead of a Yes/No field for tblEventException.IsCanned?

Answer:  tblEventException is on the outer side of a join in qryEventDates. For the records that don't have an exception, this field will therefore be Null. JET does not cope when a Yes/No field becomes Null; Access may crash if you manipulate the field further. See Why I stopped using Yes/No fields.

Question 2. Won't this approach fail if I need more than 1000 recurrences of an event?

Answer:  Add more records to tblCount if you need them. The beauty of this approach is that it does not rely on a finite number of related records created at the time you originally entered the event (as option (B) above does.) Just add the records to tblCount, and the additional instances appear.

Question 3. What's in the module ajbGlobal?

Answer:  Just the error handler. The database contains minimal code. In a real database, I suggest you use an error logger.

Question 4. Will this work with Access 97

Answer:  The concept and the queries should all work if you convert to an older version. Some features of the interface won't work, e.g. the conditional formatting on the manually entered dates (blue/bold dates in the subform shown above.)

Question 5. Why do you constantly misspell 'Canceled'?

Answer:  We use the British spelling in my part of the world.

Question 6. How can I get the sample database?

Answer:  Here 'tis.