Entering multiple preset values in a table that is different with each new record?

privateguy

I have 2 tables, one called Reminders and the other one is named Preset Events. The tables are not joined. The Preset Events table mirrors the Reminder’s table except it does not contain the Date field or the Person’s ID associated with the event. The Preset Event table contains default values for a given event, i.e. for a wedding anniversary, I may want to be reminded yearly and given a notice 2 weeks before the event. The duration for this single event is to remind me for the next 10 years. Another example is after closing a transaction, I want to be reminded a week after the event to send a thank you note. Some events would be recurring for different persons (wedding anniversaries for example) whereas some reminders may be just a onetime occurrence.

With over 40 different events, the purpose of the Preset Events table is to maintain consistency and speed of entry. Currently, I can enter a Reminder in either of 2 ways; manually in the Reminders table using a form of course or by selecting a preset event from a combo box whose record source is the Preset Events table. After selecting a preset event from the combo box, the default values are then copied to the Reminder’s table from the Preset Events table where I can then just enter the date to be reminded.

Is my method a good strategy, or is there a better way to accomplish this? Your thoughts would be greatly appreciated.

2 Weeks Ago #1

NeoPa

I would say that sounds like a good approach. Another, or at least a way to extend that, might be to include data in your extra table so that certain types of events can be created for every year going forward - like birthdays. A wedding could be set up to trigger special anniversaries at 5 year intervalsas well as lesser anniversaries each year of course.

At the end of the day, how complex you make it depends on you, but certainly what you suggest is a good basic step to simplify data entry.

2 Weeks Ago #2

isladogs

As an alternative, just use a single table and run an annual update procedure to reset each of the dates for the following year.
I have used that approach for over 15 years to reset school calendar event dates for the next academic year

2 Weeks Ago #3

privateguy

NeoPa,

I have read many of your other posts in the past. They are always well thought out and you always give deeper insight that the original poster may or may not have considered. So thank you for sharing your thoughts.

I had grappled with joining the tables, but did not think it was a good idea when I had to enter a onetime reminder. Our thought process is similar. I have other fields to give me greater flexibility. Instead of creating new reminders for each person yearly, I set mine's up one time with my desired frequency and then I set the duration for a long time in the future. Like for a birthday, I would set the duration of the reminder to be 50 years or so, so that I don't have to think about it anymore. For anniversaries, I coded the reminder to include the age of the person's birthday or anniversary (10th Wedding Anniversary). When the last reminder arrives, a popup statement advises me so that I have the option of extending it or not.

Again, thank you very much..

2 Weeks Ago #4

privateguy

Isladogs,

I had not thought about setting it up that way. I appreciate you responding.

2 Weeks Ago #5

isladogs

Just for info, the annual update process can easily manage the type of repeat events you mention as well as more complex events such as those occurring:
1. On the first Monday in September or the third Friday in December etc
2. Every two weeks on Tuesdays but in term time only
3. On the second Wednesday after Easter (which of course varies every year)
and so on...

2 Weeks Ago #6