Percentile Query Not Working as I'd Hoped

[shelby55]

Hello

In a previous post from a few years ago (161348) I had some assistance from PHV and Skip on getting a query to work for percentiles but then the project got dropped so I didn't persue. But now I'm back at it so hopefully someone can help.

I have a data query where each line of data is one hospital visit. I have unique ID, visit date, triage level and ED LOS.

What I want is to have a query that will show each admit date with the 90th percentile LOS for each triage level for that date. Percentile value isn't necessarily an actual value in the list, but a data point representing the 90th percentile in the same way Excel calculates.

What I have so far (written by PHV) is code in a module for percentile:

Public Function XthPercentile(SQL As String, ColName As String, X As Double) As Variant
Dim Percentile As Double
Dim XthRec As Double
Dim iRec As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
With rst
  If Not (.BOF Or .EOF) Then
    .MoveLast
    If X = 1 Then Percentile = .Fields(ColName)
    .MoveFirst
    If X = 0 Then Percentile = .Fields(ColName)
    If X > 0 And X < 1 Then
      XthRec = 1 + X * (.RecordCount - 1)
      iRec = Int(XthRec)
      .Move iRec - 1
      Percentile = .Fields(ColName)
      XthRec = XthRec - iRec
      If XthRec > 0 Then
        .MoveNext
        Percentile = Percentile + XthRec * (.Fields(ColName) - Percentile)
      End If
    End If
    XthPercentile = Percentile
  End If
  .Close
End With
Set rst = Nothing
End Function

In the query itself I have the code:

SELECT A.AdmDate AS Expr1, A.Triage AS Expr2, XthPercentile("SELECT LOS FROM qryEDLOS WHERE AdmDate=#" & A.AdmDate & "# AND  Triage='" & A.Triage & "' ORDER BY LOS","LOS",0.5) AS [90th Percentile]
FROM qryEDLOS
GROUP BY A.AdmDate, A.Triage;

However, this requests input of dates and triage levels and I just want an entire list grouped by date and triage. Thanks.

 

[shelby55]

Hi

Actually I got it to work so a belated thanks to PHV!!

The code is:

 SELECT qryEDLOS.AdmDate AS RegDate, Round(XthPercentile("SELECT LOS FROM qryEDLOS WHERE AdmDate=#" & qryEDLOS.AdmDate & "# ORDER BY LOS","LOS",0.9),2) AS [90th Percentile]
FROM qryEDLOS
GROUP BY qryEDLOS.AdmDate;

In the project I am using this for it will be populating an Excel workbook as the "raw data". On the summary page the user can select any admit date to see various statistics including 90th percentile LOS. But they can also see what the 90th percentile was 7 days previous to the date and 30 days previous. How would this be programmed?

I'm not sure how the syntax should look but essentially I am trying to write a statement like "if admitdate = admitdate-7,[90thPercentile],0)

Thanks.