As you found, recurring events are not the simplest thing on the planet.
The simplest approach is to create a table with fields like this:
EventID primary key
StartDate Date/Time date of the first appointment. Requiried.
PeriodType Text "d", "m", "q", or "yyyy". Required.
Freq Number how many periods between events. >= 0.
EventCount Number how many events in the series.
Next, you need a counting table from 1 to the highest number of repeating
appointments you could need (perhaps 1000?) This table has one field named
(say) CountID, type Number, primary key, and you save the table as (say)
tblCount. Enter record from *zero* to the highest number. The code in this
link can populate the table for you:
Now create a query using both tables, but with *no* join between them in the
upper pane of table design. This gives you every possible combination (a
Cartesian product), which we will use to get a record for each appointment
in the series.
In the Criteria row of the query under CountID, enter:
This limits the query to the right number of events for the series.
Now enter an expression like this in the Field row of the query:
EventDate: DateAdd([PeriodType], [CountID] * [Freq], [StartDate])
This gives you the date of each event in the series.
This approach is fully normalized and quite easy to implement. The major
limitation is that you cannot remove or reschedule one appointment in the
series. If you need that functionality, you may be able to do that with a
table of exceptions that uses an EventID and CountID and EventDate, so you
can switch the n-th appointment for a particular event to a different date
(or to Null for no date), and then outer join the above query with the
exception table to get the alternative date.
If that's not powerful enough, you can actually generate a record in a
related table for each event in the series. This gives great flexibility but
has several drawbacks:
- Doesn't cope with unlimited series (no end date)
- Harder to maintain. For example, if a change is made to the series, how to
you cascade that change to all events in the series, when some appointments
may be specific (out of the normal series)?
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.