I have a database for Oncology Research appointments. This may take some
explaining.

Let's assume I have 5 different clinical trials (protocols) opened. Each
protocol has a set criteria for when a patient needs to give blood, needs
treatment, needs an appointment, etc. (Whatever the reason.) Patient John Doe
is on protocol E2805 and Jane Smith is on the same protocol, however, John
Doe started the protocol in January and Jane started the protocol in July.
As per protocol guidelines for E2805, a patient needs to be seen after
randomization every 30 days for the first three cycles. (For instance, John
started treatment on January 2, 2008 (this is the begining of the cycle 1;
each cycle is five weeks long). Therefoere, cycle 2 starts on February 6,
2008; and his last cycle of treatment will start on March 12, 2008. During
his treatment he will also be seen by the Oncology Research Nurse every 30
days. (Start date 1/2, next appointment with Onoclogy Nurse is 2/1, then
March 3). After the first 3 cycles of treatment (medication [treatment] is
no longer given)he is then on follow-up every 4 months for the next 2 years
and then every 9 months for the remaining 3 years; where as this is a 5 year
study.

Is there code that Access can automatically update the dates of treatment,
appointments and follow-up so that it does not have to be put in manually.
Each protocol is different, however this is one example.

Thank you very much for any help.

Kathy

You could do it programmatically, but some additional rules need to be
established. For example what about appointment dates that fall on weekend
days or holidays? What about apointment times? What about the provider's
work schedule? what about appointment overlaps?

You first need to write out in a textual format what the rules are before
you ever enter a line of code. This will take some fairly complex VBA
because of the variable rules issue. It may involve some SQL, but it is too
complex for a stored query.

When I say define the rules in a text format I mean like:

1. A Protocol will be 5 weeks in length
2. A Protocol will begin on a Monday and end on the Friday of the fifth week
3. A new Protocol will begin the Monday following the end of the previous
Protocol.
4. If the Date a Protocol is scheduled to begin is an office Holiday, the
beginning date will be .....

Take it from there. Then, if you find it is more complex than you think you
can code yourself, I suggest you seek professional assistance. By defining
the rules, whomever codes the logic will know what is expected, you can test
the logic to be sure it is correct, and you will save money by having the
design work already done.

--
Dave Hargis, Microsoft Access MVP


"kjstec" wrote:

> I have a database for Oncology Research appointments. This may take some
> explaining.
>
> Let's assume I have 5 different clinical trials (protocols) opened. Each
> protocol has a set criteria for when a patient needs to give blood, needs
> treatment, needs an appointment, etc. (Whatever the reason.) Patient John Doe
> is on protocol E2805 and Jane Smith is on the same protocol, however, John
> Doe started the protocol in January and Jane started the protocol in July.
> As per protocol guidelines for E2805, a patient needs to be seen after
> randomization every 30 days for the first three cycles. (For instance, John
> started treatment on January 2, 2008 (this is the begining of the cycle 1;
> each cycle is five weeks long). Therefoere, cycle 2 starts on February 6,
> 2008; and his last cycle of treatment will start on March 12, 2008. During
> his treatment he will also be seen by the Oncology Research Nurse every 30
> days. (Start date 1/2, next appointment with Onoclogy Nurse is 2/1, then
> March 3). After the first 3 cycles of treatment (medication [treatment] is
> no longer given)he is then on follow-up every 4 months for the next 2 years
> and then every 9 months for the remaining 3 years; where as this is a 5 year
> study.
>
> Is there code that Access can automatically update the dates of treatment,
> appointments and follow-up so that it does not have to be put in manually.
> Each protocol is different, however this is one example.
>
> Thank you very much for any help.
>
> Kathy

Hi, Kathy:

Just to second Dave's advice, which is right on the money, the biggest
mistake made by amateur programmers (including myself), is to get excited
about "coding" and not put enough thought into design. It may seem like alot
of work now, but listing all the rules and other design parameters before
typing a single line of code will save you alot of grief (and expense) down
the road.

I also second Dave's advice to maybe get some professional help once you
have a clearer idea of what you're doing. Just from the information you've
provided, it sounds like you're looking at some pretty complex criteria and
validation rules. Because your application is medically oriented, I don't
think you'd want to "play around" with trying to do it yourself with the hope
of maybe getting it right. Right?

Good luck with your quest.... both the programming one and the clinical
trial!

Regards, Chris

"kjstec" wrote:

> I have a database for Oncology Research appointments. This may take some
> explaining.
>
> Let's assume I have 5 different clinical trials (protocols) opened. Each
> protocol has a set criteria for when a patient needs to give blood, needs
> treatment, needs an appointment, etc. (Whatever the reason.) Patient John Doe
> is on protocol E2805 and Jane Smith is on the same protocol, however, John
> Doe started the protocol in January and Jane started the protocol in July.
> As per protocol guidelines for E2805, a patient needs to be seen after
> randomization every 30 days for the first three cycles. (For instance, John
> started treatment on January 2, 2008 (this is the begining of the cycle 1;
> each cycle is five weeks long). Therefoere, cycle 2 starts on February 6,
> 2008; and his last cycle of treatment will start on March 12, 2008. During
> his treatment he will also be seen by the Oncology Research Nurse every 30
> days. (Start date 1/2, next appointment with Onoclogy Nurse is 2/1, then
> March 3). After the first 3 cycles of treatment (medication [treatment] is
> no longer given)he is then on follow-up every 4 months for the next 2 years
> and then every 9 months for the remaining 3 years; where as this is a 5 year
> study.
>
> Is there code that Access can automatically update the dates of treatment,
> appointments and follow-up so that it does not have to be put in manually.
> Each protocol is different, however this is one example.
>
> Thank you very much for any help.
>
> Kathy

Couldn't agree more. I spent six months sorting out oncology
treatment databases that were designed wrong. So I understand the
whole protocol bit and the cycles and the treatments and all that.
Once you get the rules sorted out, you can generate the next
appointment date pretty easily. There's code around for getting the
next business day and skipping weekends and all that, so don't worry
about that stuff. But getting the rules right is an absolute
necessity.