470,814 Members | 885 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,814 developers. It's quick & easy.

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

15 Byte
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.
Expand|Select|Wrap|Line Numbers
  1. tblReminders
  2. ReminderID PK
  3. rPersonID
  4. rDate
  5. rEvent 
  6. rLength 
  7. rDuration 
  9. tblPresetEvents
  10. PresetID PK
  11. peEvent 
  12. peLength 
  13. peDuration 
Feb 19 '21 #1
5 1656
32,311 Expert Mod 16PB
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.
Feb 19 '21 #2
357 Expert Mod 256MB
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
Feb 19 '21 #3
15 Byte

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..
Feb 20 '21 #4
15 Byte

I had not thought about setting it up that way. I appreciate you responding.
Feb 20 '21 #5
357 Expert Mod 256MB
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...
Feb 20 '21 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.