You will have a Policy table, with fields like this:
PolicyID a unique number (primary key)
ClientID links to the primary key of your Clients table
StartDate Date/Time. When the policy begins. Required.
EndDate Date/Time. When the policy expires (is due for renewal.)
Freq Number. How often payments are needed.
PeriodID Text. One of the values to use in DateAdd, e.g. "d"; "m";
"yyyy"
Now you need another table which contains the numbers 0 to 100 (or more.)
This table will have just one field of type Number, named (say) CountID, and
marked as primary key. Save the table with a name such as tblCount. Now
enter the numbers. If you prefer, you can enter them programmatically with
the code in this link:
http://allenbrowne.com/ser-39.html
Now create a query that uses both tables. There must be no line joining the
2 tables in the upper pane of the query design window. This gives you every
possible combination. Now type an expression like this into a fresh column
in the Field row:
DueDate: DateAdd([Freq], [PeriodID], [StartDate])
In the Criteria row under this field, enter:
= [StartDate] And ((<= [EndDate]) OR ([EndDate] Is Null))
The DateAdd gives you all the due dates, and the criteria limits the results
to those in the life of the policy.
