Median Calculations and Group By

[jlandis]

have a median query as follows that I need help grouping the results:

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;

This query is based on the following query named los_min_amb_work. It has
built in parameters that work fine in the median query. Allows me to
calculate a length of stay for a given time period.

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, TSheetTitle.TSheetTitleDescription
FROM Detail INNER JOIN TSheetTitle ON Detail.TSheetTitle = TSheetTitle.
TSheetTitleNumber
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));

What I want to do is group the results in the median query by the


TSheetTitleDescription so the output would be
TSheetTitleDescription1 Median LOS1
TSheetTitleDescription2 Median LOS2

Any thoughts or comments would be appreciated.

[jlandis]

Anyone that has viewed this have any ideas?

[ajetrumpet]

j,

the reason there's no response to this is probably because it's unreadable. Don't you think?

[jlandis]

Perhaps it is not unreadable rather it is not understood. By most.

[ajetrumpet]

Perhaps it is not unreadable rather it is not understood. By most.

I would venture to say that it's not understood because it's not being read. There are very few things about Access that I, and a lot of others here, don't understand, so I would say that's not entirely accurate.