Advancing a reminder date through entry in action field

[vangogh228]

I have one client that keeps coming up with 'enhancements' that I don't know how to do. I have done pretty well so far, but this one has me stumped.

The service activity requires seven reminder dates for each client. He wants me to create seven reminder date fields to facilitate this. In each of the reminder date fields, the date is actually calculated from the "Start Date" of the contract for that client. Then, for each, he wants to able to have a field for input of the activity date when the activity is actually done. THEN, he wants the reminder date to advance to the same date the following year.

To summarize:

Start Date Entered manually to show contract date.
Reminder Dates 1-7 Originally calculated from Start Date, until Activity Done dates are entered for that activity.
Activity Done 1-7 Manual date entry to show completion (each year).

The Reminder Dates then advance one year, when Activity Done is indicated.

It seems to me that I should have a set of 7 subtables for the entry of the seven Activity Done dates. Then I have to write the calculation in the Reminder Date fields to calculate normally from the Start Date if Activity Done has no entries, but to keep adding one year to that calculation until the date advances past the last Activity Done entry.

First, I don't know if I even understand what the best design would be for storing these entries.
Second, I have NO CLUE on writing a formula to advance the dates as I have described.

ANY IDEAS ON THIS ARE GREATLY APPRECIATED!!!!

Tom

 

[Travis]

Use one table linked to the Client for your Reminder Dates:

Client Table
--------------
ClientID
Name
StartDate
...


Reminder Table
-------------------
ClientID
ReminderDate
ActivityDate


Create a relationship between the two tables.

Then on the entry of a new Client (AfterInsert Event) add the First Seven records to the Reminder Table

Then on the AfterUpdate of the ActivityDate you can then insert a new record into the Reminder Table.

 

[vangogh228]

Travis:

Thanks, but there are seven different activities I need to track, and the entries can't go into the same subtable unless there is some indicating field as to which activity the input date applies to. Plus, the client wants the new date to be calculated, not manually input. Thanks. Tom

 

[Travis]

So you add a Field to the Reminder Table to say what kind of activity it is.

You can use update Queries in the On Insert Event of a new record to add the needed Reminder Records.

All this can be made automatic on the manual entry of the Start Date.