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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|