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!
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!