A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parameter query with expressions and calculations



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2009, 04:45 AM posted to microsoft.public.access.queries
Linda \(RQ\)
external usenet poster
 
Posts: 36
Default Parameter query with expressions and calculations

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







  #2  
Old December 7th, 2009, 06:56 PM posted to microsoft.public.access.queries
Dale Fye
external usenet poster
 
Posts: 2,651
Default Parameter query with expressions and calculations

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



"Linda (RQ)" wrote:

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







.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.