Transferring some calculations from Excel to Access. Hope you can help

[Ctrl-v]

Hi,

I am trying to migrate some of my things at work away from Excel and into Access. I had the below problem figured out quite well with Excel, but I am not as good with Access. I hope you can help.



I have a table called "Treatment Plans"

Columns are:

ID
Client Name
Session (This is a date field)
Treatment Plan (This is a date field)
Clinician
--------
I made a query that gives me the three months time following the Treatment Plan date. I will then show the MAX for each Client Name in a separate query. This tells me when a treatment plan needs to be done.

However, when a client first starts they do not need a treatment plan right away. Also, I can not calculate a treatment plan based on a previous treatment plan, as there hasn't been any. In this case, this is how I need to calculate:

After three Session dates, (and only after three session dates) a calculation should start being made as follows:
After the 3rd session date, the treatment plan is due in 28 days following session 3 or in 4 sessions from now, whichever comes first.
After the 4th session date, the treatment plan is due in 28 days following session 3 or in 3 sessions from now, whichever comes first.
After the 5th session date, the treatment plan is due in 28 days following session 3 or in 2 sessions from now, whichever comes first.
After the 6th session date, the treatment plan is due in 28 days following session 3 or at the next session, whichever comes first.

Once the first treatment plan has been made, I no longer need the above calculation, and I would like to only get the calculation of three months following the last treatment plan.

thank you!!

[ssanfu]

Not really enough info....

Could you walk me through dates/sessions/treatment dates?

ID	Client Name	Session	Treatment Plan	Clinician
1000	Warren Peace	??	??	Hawkeye

What is a session? How long between sessions?

When you say

This tells me when a treatment plan needs to be done.

does this mean when (the date) the treatments must be completed? Or when a treatment should/must be administered?

[Ctrl-v]

My responses are in the blue text.

Not really enough info....

Could you walk me through dates/sessions/treatment dates?

ID	Client Name	Session	Treatment Plan	Clinician
1000	Warren Peace	12/01/18		Hawkeye
1001	Warren Peace	12/08/18		Hawkeye
1002	Warren Peace	12/17/18		Hawkeye
1003	Charles First	12/20/18		Steve
1004	Allen Fine		12/22/18	Hawkeye
1005	Charles First	12/24/18		Steve
1006	Karen Tone		1/3/19	Hawkeye

This is when the client first joins. I am only interested in the session dates when the client first joins. Now that the third session was done, the calculation of the first treatment plan needs to be made. As described above, that is 28 days from the third session date, or X amount of sessions, whichever comes first, as described above.

What is a session? How long between sessions?

A session is represented by the date that a client met with a clinician (see above). No specific amount of days in between each session (although, I do not understand why it would matter how many days in between each session); it varies.

When you say

does this mean when (the date) the treatments must be completed? Or when a treatment should/must be administered?

The treatment plan is typically started and completed on the same day. Does this help?

After the treatment plan is done, I will then enter a new record recording the date that the treatment plan was completed.

Based on the above table, Warren Peace's treatment plan is due on 01/14/19 (28 days from the third session) or in 4 sessions, whichever comes first.

After the first treatment plan is completed, I will no longer need to calculate session dates and may or may not enter those dates.

Charles First, does not have any calculated due date because he never had a 3rd session.

Allen Fine's next treatment plan due date should be calculated as 03/22/19.

Karen Tone's next treatment plan due date should be calculated as 04/03/19.

Both of these are three months after the date of their last treatment plan.