How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

[cMMMMMoira]

Hi there,
I'm trying to find the 5th, 10th, ...,95th percentiles using the ACCESS.

The dataset(tblGwMeasurement) contains five columns, which are ID, MP_ID, Depth, Temperature and ElectricalConductivity respectively. I have to find the 5th, 10th, ...,95th percentiles and their corresponding temperature values(and also Electrical conductivity) for each meter interval(e.g. 0-1m,1-2m,...,143-144m). How can I achieve it by sql or module?

I'm a green hand in ACCESS. I tried a lot, but the closest I achieved is to calculate percentile values for each record, which is not my goal. Could you help me? Thank you so much!

 

[cMMMMMoira]

I'm so sorry that I failed to upload the pictures for my original dataset and the final format that I want to achieve. I hope this time it works.

 

[MajP]

I wrote this a very long time ago. I made them like Domain aggregate functions. For some reason I did two versions. Test to see if these work.

Public Function DPercentile(expr As String, domain As String, Percentile As Double) As Variant
    Dim strSelect As String
    Dim strSelectOpposite As String
    Dim strSQL As String
    Dim OppositePercentile As Double
    
    OppositePercentile = 100 - Percentile
    
    strSelect = "SELECT TOP " & Percentile & " PERCENT [" & expr & "] FROM [" & domain & "]"
    strSelect = strSelect & " WHERE NOT [" & expr & "] IS NULL ORDER BY [" & expr & "]"
    Debug.Print strSelect
    strSelectOpposite = "SELECT TOP " & OppositePercentile & " PERCENT [" & expr & "] FROM [" & domain & "]"
    strSelectOpposite = strSelectOpposite & " WHERE NOT [" & expr & "] IS NULL ORDER BY [" & expr & "] DESC"
    strSQL = "SELECT (Max(x." & expr & ")+Min(y." & expr & "))/2 AS Median FROM (" & strSelect & ") AS x, (" & strSelectOpposite & ") AS y "
    Debug.Print strSQL
    DPercentile = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot).Fields("Median").Value
End Function
Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
    Dim strSQL As String
    Dim N As Integer
    Dim nSubk As Double
    Dim vSubk As Variant
    Dim vSubkPlus1 As Variant
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT " & expr & " FROM " & domain
    strSQL = strSQL & " WHERE NOT " & expr & " IS NULL ORDER BY " & expr
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    If Not (rs.EOF And rs.BOF) Then
      rs.MoveLast
      rs.MoveFirst
    Else
      Exit Function
    End If
    
    N = rs.RecordCount
    
    nSubk = Percentile / 100 * (N - 1) + 1
    'using NIST nSubK = Percentile / 100 * (N+1)
    
    If nSubk = 1 Then
      DPercentileExcel = rs.Fields(expr)
    ElseIf nSubk = N Then
      rs.MoveLast
      DPercentileExcel = rs.Fields(expr)
    Else
      rs.AbsolutePosition = nSubk - 1     '0 based
      vSubk = rs.Fields(expr)
      rs.AbsolutePosition = nSubk         '0 based
      vSubkPlus1 = rs.Fields(expr)
      'Debug.Print nSubk
      'Debug.Print vSubk
      'Debug.Print vSubkPlus1
      DPercentileExcel = vSubk + (nSubk - Int(nSubk)) * (vSubkPlus1 - vSubk)
    End If
End Function


Public Sub testPercentile()
 Debug.Print DPercentile("fldData", "tblOne", 30)
End Sub

Public Sub testPercentileExcel()
 Debug.Print DPercentileExcel("fldData", "tblOne", 0)
End Sub

 

[MajP]

If you can post some data in excel of access that would help. These functions were designed to return the value of the percentile for the field in question. You seem to want to return a record that is at that percentile.



Something like this instead

Public Function DPercentileRecord(IDfield As String, Expr As String, Domain As String, ByVal Percentile As Double) As Variant
    Dim strSQL As String
    Dim N As Integer
    Dim N_k As Long
    Dim rs As DAO.Recordset
    'IDField: Name of the Primary key field or unique ID
    'Expr: The Field to sort on
    'Domain: The query or table name
    'Percentile: The Percentile
    
    strSQL = "SELECT " & IDfield & ", " & Expr & " FROM " & Domain
    strSQL = strSQL & " WHERE NOT " & Expr & " IS NULL ORDER BY " & Expr
    
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    If Not (rs.EOF And rs.BOF) Then
      rs.MoveLast
      rs.MoveFirst
    Else
      Exit Function
    End If
    
    N = rs.RecordCount
    
    N_k = Int((Percentile / 100 * (N - 1)) + 1)
   
    'There are three interpretations of this rank.
    'I am going to round up
    Debug.Print N_k
    rs.AbsolutePosition = N_k - 1    '0 based
    DPercentileRecord = rs.Fields(IDfield)
End Function

You can then make a table with the values 5, 10, 15, .... . Then use that in a query to get the IDs for the the percentile records

qryPercentileIDs
Code:
Select PercentileNumber, DpercentileRecord("ID","Depth","TblGWMeasurement") AS MeasuermentID from TblPercentile
Now join qryPecentileIDs to tblGWMeasurement to show the related information for each percentile.

Now I am not sure if this is totally correct. There are a couple interpretations on the percentile. When the rank comes out to a integer this is OK, but what to do when it is not. Assume you have 11 records and want the 50th percentile. Then it is the six record because there are 5 below and 5 records above the 6th record. But if you have ten records you find the average value of the 5th and 6th record to get a value, but you want a record. So which one do you want, the 5th or sixth. I rounded up, or you could straight round.

 

[cMMMMMoira]

Thank you MajP. Your code works. But do you know how to add a condition that tells the access to first pick out the depths which are between number a and number b and then run the code?

 

[MajP]

But do you know how to add a condition that tells the access to first pick out the depths which are between number a and number b and then run the code?

make that query first
qryBetween

  Select ID, Depth From TblGWMeasurement WHERE Depth Between A AND B ORDER BY Depth

now use qryBetween in your function like

DpercentileRecord("ID","Depth","qryBetween")