complicated condition of date calculation

[sanych10]

I can't seem to find a solution to a logical problem :
Here is the quick outline
I have a patient that is waiting for a placement.
All data is in the table that has following design (fields)
ID - autonumber
Bed - text
nName - text
stream - text
ref-date/time
rai - date/time
refraidif - text
choice - date/time
rchdif - text
placement - date/time
chpldif - text
expdc - yes/no
notalc1 - date/time
notalc2- date/time
notalcd - text

Process: we submit application to CCAC for placements. They have deadlines to meet:
From Submit application (ref) to rai is 3 days.
From rai to choice - 7 days
from choices to placement 14 days.
This placement consists of 3 steps:
1: Submit application date and RAI date (first response )
2. From Rai date to Choices date
3 From Choices to Placecement
I needed to calculate the time difference between the steps (which I did):
Here is my logic:
SELECT ALC.Bed, ALC.nName, ALC.stream, ALC.ref, ALC.rai, IIf([rai] Is Null,Date()-[ref],[rai]-[ref]) AS refraidif, ALC.choice, IIf([choice] Is Null,Date()-[rai],[choice]-[rai]) AS rchdif, ALC.expdc
FROM ALC
WHERE (((ALC.stream)="ltc" Or (ALC.stream)="cc") AND ((ALC.ref) Is Not Null) AND ((ALC.rai) Is Not Null) AND ((ALC.choice) Is Null) AND ((IIf([choice] Is Null,Date()-[rai],[choice]-[rai]))>7) AND ((ALC.expdc)=No));

but what happens: sometimes patients gets worse and those days when he is worse (notalc) we should not count(sort of counter stops). So what I did I introduced
3 new fields notalc1 - first date when he becomes sick (will be entered manually), notalc2 - last day when he becomes not alc and notalcd - how many days he was sick.

I dont know how to incorporate the automatic deduction of those days from the correct step in the patient placement process:
for example if I submit application on the 1.05 and get rai on 5.05 and then get choices on the 10.05 and patient gets worse on the 8.05 and gets better on the 10.05 these 2 days whould get deducted from time range between rai to choices,not ref to rai (because notalc1 is 8.05 which false under the rai to choices time range).
I have created this,but it does not take into consideration the time range - really have no idea how to do it!!!!

SELECT ALC.Bed, ALC.nName, ALC.stream, ALC.ref, ALC.rai, IIf([notalcd] And [rai] Is Null,Date()-[ref],[rai]-[ref]-[notalcd]) AS refraidif, ALC.choice, IIf([choice] Is Null,Date()-[rai],[choice]-[rai]) AS rchdif, ALC.placement, IIf([placement] Is Null,Date()-[choice],[placement]-[choice]) AS chpldif, ALC.expdc, ALC.notalc1, ALC.notalc2, IIf([notalc2] Is Null,Date()-[notalc1],[notalc2]-[notalc1]) AS notalcd
FROM ALC
WHERE (((ALC.stream)="LTC" Or (ALC.stream)="CC" Or (ALC.stream)="HF") AND ((ALC.expdc)=No));

Community help is really appreciated.
Thanks a lot!

 

[SkipVought]

hi,

if I submit application on the 1.05 and get rai on 5.05 and then get choices on the 10.05

notalc1 is 8.05 which false under

what is "the 1.05"???

Are these supposed to be DATES?

You are posting JARGON, which means NOTHING to the unwashed masses.

I think that you have a complex requirement that might require some VBA CODE in order to solve.

AND you must post your question in terms that are clear, concise and complete; understandable to anyone not familiar with your world.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sanych10]

apologies.
1.05 is 1st May.

 

[MajP]

This is confusing with all the buisiness logic and jargon, but my first hunch is that I would not design it this way because it appears that some events have ranges some just a start date. Also when you start adding fields to solve exceptions it usually means your table is not normal. My table would be

tblEvents
eventID
eventTitle (Admission,Referral, RAI, etc)
eventType (single date, or range)

tblPersonEvents
patientID
eventID
eventStart
eventEnd (same as start if not a range)
eventNote

 

[sanych10]

thanks a lot.
logic is the following
i calculate time difference between events. if at the time of event I dont have the event date. I calculate based on the todays date minus first event date. I have 3 events that i calculate this way.
these events represent the placement process of a patient from a hospital to a nursing home. Nursing homes they have to meet the deadline:
from 1st even to a second there have to be no more than 3 days.
from second to a third - seven days
but there is an exception came out: if during the placement process patient feels bad. those days when he felt bad should not be counted towards their deadline.
for example if the patient felt bad during the second event than instead of 7 days I have to calculate 7-felt bed days.

 

[SkipVought]

Do you have your date data stored as 1.05 or is that just your personal notation?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sanych10]

yes it is stored as date/time field and gets entered by the user.
thanks a lot!