Parameter query with expressions and calculations

[Linda \(RQ\)]

Hi Everyone,

I am using Access 2003.

I have been working on this query for a few weeks using my Access book,
examples from this group and google searches. I am pretty close to getting
what I want but I am getting frustrated and mixed up and I can't seem to
figure it out. I'm starting to get mixed up trying to explain this too so I
am going to post and let you guys help me organize my thoughts...geesh! In
regular words here is what I want.

My database is of patients and information about the therapy we are giving
them and how long they are on that therapy.

I want to calculate how many days we had patients in a certain unit on
ventilators (VentLOSDays). I get the list of patients and the records from
this query and then export it to excel where I tally up the total days for
all the patients as well as look up other info and add it to that sheet. My
query pulls up patients from the NBICU who were on a vent (thpy ID 70, 81 or
91). That part seems to be working well. I made some expressions that are
sort of working

CalcStartDate is...If the patient was on the vent before the start date that
I enter in my parameter query then the date I enter will be the
CalcStartDate at midnight in this fieldCalcEndDate is....If the patient was
on the vent after the end date that I enter than that date is the
CalcEndDate at midnight.

If the patient was started and ended on the vent between the dates I enter,
those dates and times are returned in the CalcStartDate and CalcEndDate
fields for
those patients.

I want my VentLOSDays to calculate the days on the vent from the
CalcStartDate and CalcEndDate

Below is my sql but I used the query grid, I just post this because it
appears you guys can read this better than my explainations of what I put
where in the query grid <g> Below that is a few examples of data returned
for a few records.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;SELECT
tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaID, tblAreaList.AreaName,
tblTherapyType.TherapyTypeID, tblTherapyType.TherapyDesc,
tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm, IIf([ThpyStDtTm]<[Enter Start
Date]-1,[Enter Start Date],[ThpyStDtTm]) AS CalcStartDate,
IIf(Nz([ThpyEndDtTm],[Enter End Date]),IIf([ThpyEndDtTm]>=[Enter End
Date]+1,[Enter End Date],[ThpyEndDtTm]),[ThpyEndDtTm]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHoursFROM (tblPatients INNER JOIN (tblAreaList INNER JOIN
tblPtLocation ON tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON
tblPatients.PtID = tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID = tblPtThpy.PtID_fk GROUP BY tblPatients.PtLName,
tblPatients.PtID, tblPatients.AdmitNum, tblPtThpy.PtThpyID,
tblAreaList.AreaID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaID)=20) AND ((tblTherapyType.TherapyTypeID)=70 Or
(tblTherapyType.TherapyTypeID)=81 Or (tblTherapyType.TherapyTypeID)=97) AND
((tblPtThpy.ThpyStDtTm)<=[Enter End Date]+1) AND
((IIf(Nz([ThpyEndDtTm],[Enter End Date]),IIf([ThpyEndDtTm]>=[Enter End
Date]+1,[Enter End Date],[ThpyEndDtTm])[ThpyEndDtTm])) Between [Enter Start
Date] And [Enter End Date])) ORDER BY tblPatients.PtLName;


Returned Data for a few records

ThpyStDtTm 9/24/09 and ThpyEndDtTm 10/13/09 16:50
CalcStartDate is 10/1/09 0:00 CalcEnd Date is 10/13/09 16:50
VentLOSDays is 12.7 (This is correct)

ThpyStDtTm 9/24/09 3:54 and ThpyEndDtTm 11/7/09 14:40
CalcStartDate is 10/1/09 0:00 and my CalcEndDate is 10/31/09 0:00
VentLOSDays returns 37.61 (Incorrect) should be (31 days)

ThpyStDtTm 10/30/09 19:28 and ThpyEndDtTm 11/8/09.
CalcStartDate is 10/30/09 19:20 and CalcEndDate is 10/31/09 0:00
Vent LOS returns 8.63 (Incorrect) should be (1.2 days)

Thanks,
Linda

 

[Allen Browne]

So you want the number of days between ThpyStartDtTm and ThpyEndDtTm that
overlap with the criteria range in your query.

The basic logic for identifying overlapping dates between 2 ranges is that:
- A starts before B ends, AND
- B starts before A ends.

Draw some timeline examples, and you'll see how this works, e.g.:
Case 1:
A --------------------------
B -----
Case 2:
A -----------
B ------
and so on.

So, to limit your query to those records where there are overlapping dates,
replace your HAVING clause with a WHERE clause like this:
WHERE (tblAreaList.AreaID = 20)
AND (tblTherapyType.TherapyTypeID IN (70, 81, 97)
AND (tblPtThpy.ThpyEndDtTm <= [Enter Start Date])
AND ([Enter End Date] <= tblPtThpy.ThpyStartDtTm)

Once you have the records there this is an overlap, you can then calculate
the number of days within that range, by choosing the later of the 2 start
dates and the earlier of the 2 end dates, and subtracting:
IIf(tblPtThpy.ThpyEndDtTm < [Enter End Date], [Enter End Date],
tblPtThpy.ThpyEndDtTm) -
IIf(tblPtThpy.ThpyStartDtTm < [Enter Start Date], [Enter Start Date],
tblPtThpy.ThpyStartDtTm)

You'll need to adjust that by adding 1 etc, but that's the basic logic.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


[snip]

I want to calculate how many days we had patients in a certain unit on
ventilators (VentLOSDays). I get the list of patients and the records
from this query and then export it to excel where I tally up the total
days for all the patients as well as look up other info and add it to that
sheet. My query pulls up patients from the NBICU who were on a vent (thpy
ID 70, 81 or 91). That part seems to be working well. I made some
expressions that are sort of working

CalcStartDate is...If the patient was on the vent before the start date
that I enter in my parameter query then the date I enter will be the
CalcStartDate at midnight in this fieldCalcEndDate is....If the patient
was on the vent after the end date that I enter than that date is the
CalcEndDate at midnight.

[snip]

 

[Dale Fye]

Allen,

Shouldn't that read:

WHERE (tblAreaList.AreaID = 20)
AND (tblTherapyType.TherapyTypeID IN (70, 81, 97)
AND (tblPtThpy.ThpyStartDtTm <= [Enter End Date])
AND (tblPtThpy.ThpyEndDtTm >= [Enter Start Date])

The way you have it written will identify those which End before [Enter
Start Date] AND start after [Enter End Date], and I cannot imagine that there
are any of those.

----
Dale

So you want the number of days between ThpyStartDtTm and ThpyEndDtTm that
overlap with the criteria range in your query.

The basic logic for identifying overlapping dates between 2 ranges is that:
- A starts before B ends, AND
- B starts before A ends.

Draw some timeline examples, and you'll see how this works, e.g.:
Case 1:
A --------------------------
B -----
Case 2:
A -----------
B ------
and so on.

So, to limit your query to those records where there are overlapping dates,
replace your HAVING clause with a WHERE clause like this:
WHERE (tblAreaList.AreaID = 20)
AND (tblTherapyType.TherapyTypeID IN (70, 81, 97)
AND (tblPtThpy.ThpyEndDtTm <= [Enter Start Date])
AND ([Enter End Date] <= tblPtThpy.ThpyStartDtTm)

Once you have the records there this is an overlap, you can then calculate
the number of days within that range, by choosing the later of the 2 start
dates and the earlier of the 2 end dates, and subtracting:
IIf(tblPtThpy.ThpyEndDtTm < [Enter End Date], [Enter End Date],
tblPtThpy.ThpyEndDtTm) -
IIf(tblPtThpy.ThpyStartDtTm < [Enter Start Date], [Enter Start Date],
tblPtThpy.ThpyStartDtTm)

You'll need to adjust that by adding 1 etc, but that's the basic logic.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


[snip]

I want to calculate how many days we had patients in a certain unit on
ventilators (VentLOSDays). I get the list of patients and the records
from this query and then export it to excel where I tally up the total
days for all the patients as well as look up other info and add it to that
sheet. My query pulls up patients from the NBICU who were on a vent (thpy
ID 70, 81 or 91). That part seems to be working well. I made some
expressions that are sort of working

CalcStartDate is...If the patient was on the vent before the start date
that I enter in my parameter query then the date I enter will be the
CalcStartDate at midnight in this fieldCalcEndDate is....If the patient
was on the vent after the end date that I enter than that date is the
CalcEndDate at midnight.

[snip]

.

 

[Dale Fye]

Rather than use all of the IIF( ) function calls in your Select clause, I
have a couple of functions that I use for this purpose. fnMin( ) and fnMax(
) accept any number of parameters, but in your case you would only pass two.
To get the "start date" of your computation, you would use:

fnMax(thpyStartDtTm, [Enter Start Date]) as CalcStartDate

To get the end date, you would use:

fnMin(thpyEndDtTm, [Enter End Date]) as CalcEndDate

The other advantage of these functions, is that they accept NULLs being
passed to them, and just ignore them, so you don't have to test if the end
date is null.

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function
Public Function fnMax(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) > myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMax = myVal

End Function

----
HTH
Dale

Hi Everyone,

I am using Access 2003.

I have been working on this query for a few weeks using my Access book,
examples from this group and google searches. I am pretty close to getting
what I want but I am getting frustrated and mixed up and I can't seem to
figure it out. I'm starting to get mixed up trying to explain this too so I
am going to post and let you guys help me organize my thoughts...geesh! In
regular words here is what I want.

My database is of patients and information about the therapy we are giving
them and how long they are on that therapy.

I want to calculate how many days we had patients in a certain unit on
ventilators (VentLOSDays). I get the list of patients and the records from
this query and then export it to excel where I tally up the total days for
all the patients as well as look up other info and add it to that sheet. My
query pulls up patients from the NBICU who were on a vent (thpy ID 70, 81 or
91). That part seems to be working well. I made some expressions that are
sort of working

CalcStartDate is...If the patient was on the vent before the start date that
I enter in my parameter query then the date I enter will be the
CalcStartDate at midnight in this fieldCalcEndDate is....If the patient was
on the vent after the end date that I enter than that date is the
CalcEndDate at midnight.

If the patient was started and ended on the vent between the dates I enter,
those dates and times are returned in the CalcStartDate and CalcEndDate
fields for
those patients.

I want my VentLOSDays to calculate the days on the vent from the
CalcStartDate and CalcEndDate

Below is my sql but I used the query grid, I just post this because it
appears you guys can read this better than my explainations of what I put
where in the query grid <g> Below that is a few examples of data returned
for a few records.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;SELECT
tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaID, tblAreaList.AreaName,
tblTherapyType.TherapyTypeID, tblTherapyType.TherapyDesc,
tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm, IIf([ThpyStDtTm]<[Enter Start
Date]-1,[Enter Start Date],[ThpyStDtTm]) AS CalcStartDate,
IIf(Nz([ThpyEndDtTm],[Enter End Date]),IIf([ThpyEndDtTm]>=[Enter End
Date]+1,[Enter End Date],[ThpyEndDtTm]),[ThpyEndDtTm]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHoursFROM (tblPatients INNER JOIN (tblAreaList INNER JOIN
tblPtLocation ON tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON
tblPatients.PtID = tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID = tblPtThpy.PtID_fk GROUP BY tblPatients.PtLName,
tblPatients.PtID, tblPatients.AdmitNum, tblPtThpy.PtThpyID,
tblAreaList.AreaID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaID)=20) AND ((tblTherapyType.TherapyTypeID)=70 Or
(tblTherapyType.TherapyTypeID)=81 Or (tblTherapyType.TherapyTypeID)=97) AND
((tblPtThpy.ThpyStDtTm)<=[Enter End Date]+1) AND
((IIf(Nz([ThpyEndDtTm],[Enter End Date]),IIf([ThpyEndDtTm]>=[Enter End
Date]+1,[Enter End Date],[ThpyEndDtTm])[ThpyEndDtTm])) Between [Enter Start
Date] And [Enter End Date])) ORDER BY tblPatients.PtLName;


Returned Data for a few records

ThpyStDtTm 9/24/09 and ThpyEndDtTm 10/13/09 16:50
CalcStartDate is 10/1/09 0:00 CalcEnd Date is 10/13/09 16:50
VentLOSDays is 12.7 (This is correct)

ThpyStDtTm 9/24/09 3:54 and ThpyEndDtTm 11/7/09 14:40
CalcStartDate is 10/1/09 0:00 and my CalcEndDate is 10/31/09 0:00
VentLOSDays returns 37.61 (Incorrect) should be (31 days)

ThpyStDtTm 10/30/09 19:28 and ThpyEndDtTm 11/8/09.
CalcStartDate is 10/30/09 19:20 and CalcEndDate is 10/31/09 0:00
Vent LOS returns 8.63 (Incorrect) should be (1.2 days)

Thanks,
Linda







.