Median

[Doc94]

I need some help with a median calculation. I can calculate the median length of stay for a group of patients with the following query I picked up.

SELECT Avg([R1]) AS Median
FROM [

SELECT Max(Q1.R2) AS R1

FROM

(SELECT TOP 50 PERCENT LOS_min_amb_work.los AS R2

FROM LOS_min_amb_work

ORDER BY LOS_min_amb_work.los ASC) AS Q1

UNION

SELECT Min(Q2.R2) AS R1

FROM

(SELECT TOP 50 PERCENT LOS_min_amb_work.los AS R2

FROM los_min_amb_work

ORDER BY LOS_min_amb_work.los DESC) AS Q2

]. AS Q3;

I have now been asked to calculate a median length of stay and group them by diagnosis. The LOS_min_amb_work.los is a query as follows where the TSheetTitleDescription is the diagnosis field I need to group by.
PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
SELECT Detail.[ED#], Detail.HospitalArrivalTime, TimeValue([HospitalArrivalTime]) AS HATTime, Detail.DispositionTime, IIf(([DispositionTime]-[HATTime])<0,(([DispositionTime]-[HATTime])+1)*1440,([DispositionTime]-[HATTime])*1440) AS LOS, Detail.PhysicianExamTime, IIf([DispositionTime]-[PhysicianExamTime]<0,([DispositionTime]-[PhysicianExamTime]+1)*1440,([DispositionTime]-[PhysicianExamTime])*1440) AS MDToDT, [Enter Beginning Date] AS Beg, [Enter Ending Date] AS [End], DateValue([Detail]![HospitalArrivalTime]) AS HAD, IIf([DispositionTime]-[DispositionOrderTime]<0,([DispositionTime]-[DispositionOrderTime]+1)*1440,([DispositionTime]-[DispositionOrderTime])*1440) AS DOTToDT, Detail.EDBedTime, Detail.DispositionOrderTime, IIf([DispositionTime]-[PhysicianExamTime]<0,([DispositionTime]-[PhysicianExamTime]+1)*1440,([DispositionTime]-[PhysicianExamTime])*1440) AS MDoDOT, Detail.DispositionCode, Detail.Lastname, Detail.TSheetTitle
FROM Detail
WHERE (((Detail.HospitalArrivalTime) Between [Enter Beginning Date] And ([Enter Ending Date]+1)) AND ((Detail.DispositionCode)=1 Or (Detail.DispositionCode)=3 Or (Detail.DispositionCode)=15 Or (Detail.DispositionCode)=21 Or (Detail.DispositionCode)=22 Or (Detail.DispositionCode)=23 Or (Detail.DispositionCode)=31));

Any help in getting the median query to function with grouping would be greatly appreciated.

 

[Doc94]

I think I can add GROUP BY TSheetTitleDescription in the first query as it is a field in the LOS_min_amb_work query I am just not sure where to put it.

 

[Doc94]

I also have this function but can not get it to work correctly within a query. Or anywhere else for that matter.
Function DMedian(expr As String, domain As String, Optional criteria As String) As DoubleDim dbs As DatabaseDim rst As RecordsetDim middleIndex As Integerdim numberOfRecords as IntegerSet dbs = CurrentDbIf Len(criteria) <> 0 ThenSet rst = dbs.OpenRecordset("select " & expr & " from " & domain & " where " & criteria & _ " order by " & expr)ElseSet rst = dbs.OpenRecordset("select " & expr & " from " & domain & _ " order by " & expr)'Make sure the spaces inside the quotes are preserved, otherwise your SQL will'not be syntactically correct and Access will complain!End IfIf rst.BOF ThennumberOfRecords = 0Elserst.MoveLastnumberOfRecords = rst.RecordCount'You need the Movelast to get the correct record count out of a recordsetEnd IfIf numberOfRecords = 0 ThenDMedian = 0'We assume that the median is 0 when the number of records is zeroElseIf numberOfRecords = 1 ThenDMedian = rst(expr)'If the number of records is 1, the value of the expression in that record is the medianElsemiddleIndex = Int(0.5 * (numberofrecords - 1) + 1)'MiddleIndex now points to the middle of the recordset if the recordset contains an odd number of records.'If the recordset has an even number of records, it points just above the midpoint of the recordset.rst.MoveFirstrst.Move (middleIndex - 1)DMedian = rst(expr)'if the number of records is odd, we are doneIf numberofrecords Mod 2 = 0 Then'the number of records is an even number rst.MoveNext DMedian = (DMedian + rst(expr)) / 2# 'Take the average between the values in this record and the next record 'Note that the next record will point just below the midpoint of the recordsetEnd IfEnd Ifrst.CloseSet rst = Nothingdbs.CloseSet dbs = Nothing'Cleanup everything before leaving the functionEnd Function


Read more:

http://blogannath.blogspot.com/2009/11/microsoft-access-tips-tricks-finding.html#ixzz1EX8eVEhk