Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-21-2018, 07:13 AM   #1
cMMMMMoira
Newly Registered User
 
Join Date: Jun 2018
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
cMMMMMoira is on a distinguished road
Question How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

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!


Last edited by cMMMMMoira; 06-21-2018 at 07:17 AM. Reason: to add photos
cMMMMMoira is offline   Reply With Quote
Old 06-21-2018, 07:18 AM   #2
cMMMMMoira
Newly Registered User
 
Join Date: Jun 2018
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
cMMMMMoira is on a distinguished road
Re: How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

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.

Last edited by cMMMMMoira; 06-21-2018 at 07:27 AM.
cMMMMMoira is offline   Reply With Quote
Old 06-21-2018, 07:42 AM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 290
Thanks: 3
Thanked 69 Times in 67 Posts
MajP will become famous soon enough
Re: How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

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.

Code:
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 is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
cMMMMMoira (06-25-2018)
Old 06-21-2018, 01:30 PM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 290
Thanks: 3
Thanked 69 Times in 67 Posts
MajP will become famous soon enough
Re: How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

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

Code:
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.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
cMMMMMoira (06-25-2018)
Old 06-25-2018, 02:15 AM   #5
cMMMMMoira
Newly Registered User
 
Join Date: Jun 2018
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
cMMMMMoira is on a distinguished road
Re: How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

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?
cMMMMMoira is offline   Reply With Quote
Old 07-02-2018, 12:56 PM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 290
Thanks: 3
Thanked 69 Times in 67 Posts
MajP will become famous soon enough
Re: How to select the 5th, 10th...percentiles in Access?Thank you soooooo much!

Quote:
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
Code:
  Select ID, Depth From TblGWMeasurement WHERE Depth Between A AND B ORDER BY Depth
now use qryBetween in your function like
Code:
DpercentileRecord("ID","Depth","qryBetween")

MajP is offline   Reply With Quote
Reply

Tags
modules , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
looking to return 2nd/5th/10th from last record NicholasP Queries 13 10-24-2014 07:42 AM
Multi Select Listbox Access 2003 Code gives syntax error in Ms Access 2010 papic1972 Forms 12 10-27-2013 05:52 PM
How to select a table name with whitespaces in access 2007 from outside access Jelte General 9 02-26-2010 12:38 AM
Finding Percentiles pickslides Queries 4 05-12-2008 09:25 PM
Calculating Percentiles tbartow Modules & VBA 1 03-04-2002 03:45 PM




All times are GMT -8. The time now is 08:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World