Newbie with two questions involving automatic updating of dates

[Hog80ci]

I work in a residential drug treatment program and am in the process of building a database to track various info on our patients. My questions are:

1. We often have a wait list of people waiting to get into the program. I have a field AddedToWaitlist and another TimeonWaitlist. I want TimeonWaitlist to automatically update each day a patient remains on the list.



2. This is a 92 day program. I have a DateofAdmission field and a DateofGraduation field. I want the DateofGraduation to automatically add 91 days to the DateofAdmission and show the date.

Is any of this even possible?

Thank you very much in advance for your help.

James

[pbaldy]

Possible? Sure, but:

1) In a word, don't. There's a general rule-of-thumb in database design that you don't store calculated values. It's simple to calculate the time on the wait list from the added to date with the DateDiff() function and the Date() function which returns today's date. You can even use the admission date or the current date when there is no admission date (they're still waiting) with the Nz() function.

2) Similarly, if it's always 92 days, there's no need to store the date. You can calculate it with DateAdd() from the admission date.

[Hog80ci]

Possible? Sure, but:

1) In a word, don't. There's a general rule-of-thumb in database design that you don't store calculated values. It's simple to calculate the time on the wait list from the added to date with the DateDiff() function and the Date() function which returns today's date. You can even use the admission date or the current date when there is no admission date (they're still waiting) with the Nz() function.

2) Similarly, if it's always 92 days, there's no need to store the date. You can calculate it with DateAdd() from the admission date.

Thanks very much. I will try that. Obviously shows I'm struggling a bit here.

James

[pbaldy]

No problem, and welcome to the site! Post back if you get stuck.