Calculating decile

[j235]

Hi,
I have one problem with calculation decile in Access. Namely I want to calculate 10 th,20 th,30 th,40th,50th,60th,70th,80th,90th and 100th decile for numbers in columns c1 and c2 in Table.
I have SQL code for Access, but with this code I can calculate only one value for decile (For example with code below I can calculate decile only for 50th percent and for one column “c1”, but I ‘cant calculate for all 10 category of decile for two columns C1 and C2).
Can anybody help me with this issue?
Thank you in advance.

SQL CODE
SELECT Max([C1]) AS Percentile
FROM
(SELECT TOP 50 PERCENT [C1]
FROM [Tabele]
ORDER BY [C1] ASC);

Table​

c1[FONT=&quot][/FONT]​

c2[FONT=&quot][/FONT]​

1[FONT=&quot][/FONT]​

252[FONT=&quot][/FONT]​

2[FONT=&quot][/FONT]​

525[FONT=&quot][/FONT]​

5[FONT=&quot][/FONT]​

21[FONT=&quot][/FONT]​

10[FONT=&quot][/FONT]​

25[FONT=&quot][/FONT]​

150[FONT=&quot][/FONT]​

2665[FONT=&quot][/FONT]​

15812[FONT=&quot][/FONT]​

2511[FONT=&quot][/FONT]​

1515[FONT=&quot][/FONT]​

2515[FONT=&quot][/FONT]​

1518[FONT=&quot][/FONT]​

325[FONT=&quot][/FONT]​

1548[FONT=&quot][/FONT]​

2515[FONT=&quot][/FONT]​

1588[FONT=&quot][/FONT]​

25[FONT=&quot][/FONT]​

1288[FONT=&quot][/FONT]​

58[FONT=&quot][/FONT]​

12558[FONT=&quot][/FONT]​

98[FONT=&quot][/FONT]​

1555[FONT=&quot][/FONT]​

596[FONT=&quot][/FONT]​

 

[MarkK]

A recordset has a PercentPosition property, which, when you set it, causes the recordset to navigate to the record at that location. So to get your deciles you could do something like . . .

private const SQL_SELECT as string = "SELECT C1 FROM TableE ORDER BY C1"

private m_rst as dao.recordset

private property get Recordset as dao.recordset
[COLOR="Green"]'   returns the recordset, creates it if required[/COLOR]
    if m_rst is nothing then set m_rst = currentdb.openrecordset(SQL_SELECT)
    set Recordset = m_rst
end property

Function GetDecile(AtPercent as Integer) As Long
[COLOR="Green"]'   navigates the recordset to the indicated decile, and returns that value[/COLOR]
    with Recordset
       [COLOR="Blue"].PercentPosition[/COLOR] = AtPercent
       GetDecile = .Fields(0).Value
    end with
End Function

Sub Test12903481
[COLOR="Green"]'   prints each decile to immediate pane[/COLOR]
    dim i as integer
    for i = 1 to 10
       debug.print i, GetDecile(i * 10)
    next
End Sub

See whats going there?

 

[jdraw]

Cross posted http://www.accessforums.net/showthread.php?t=58554

Tell readers about the cross post and leave a link(s)

Here is why!!

PS. Just saw your post--- Very nice Markk !!

 

[arnelgp]

maybe a union query will also do:

SELECT Max([C1]) AS Percentile
FROM
(SELECT TOP 10 PERCENT [C1]
FROM [Tabele]
ORDER BY [C1] ASC)
UNION
SELECT Max([C1]) AS Percentile
FROM
(SELECT TOP 20 PERCENT [C1]
FROM [Tabele]
ORDER BY [C1] ASC)
UNION
SELECT Max([C1]) AS Percentile
FROM
(SELECT TOP 30 PERCENT [C1]
FROM [Tabele]
ORDER BY [C1] ASC)
...
...
and so on

 

[Galaxiom]

Another alternative would involve applying Partition() to their numbered ordering.

 

[jdraw]

Here's another (But I still like Markk's approach)

'---------------------------------------------------------------------------------------
' Procedure : decileStuff
' Author    : ...jd...
' Date      : 08/03/2016
' Purpose   : Related to  Calculating Decile
'http://www.access-programmers.co.uk/forums/showthread.php?t=285149
'
' Took table with some data, created logic to calculate and print to immediate window
' each decile for each field requested.
'---------------------------------------------------------------------------------------
'
Sub decileStuff()
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
10       On Error GoTo decileStuff_Error

20        Set db = CurrentDb
          Dim MySQL As String, tablename As String
30        tablename = "DalhousieLake"  'an existing table
          Dim P As Integer, px As Integer
          Dim i As Integer, j As Integer
          Dim fldname(1) As String
40        fldname(0) = "[Gauge Reading (MASL)]"   'your c1 field name
50        fldname(1) = "[Historical Avg]"         'your c2 field name
60        P = 10
70        For i = 0 To 1  ' two fields involved
80            For j = 1 To 10  '10 percentiles/deciles
90                px = P * j   'PX represents percentage

100               MySQL = "SELECT Max(" & fldname(i) & ") AS Percentile FROM " _
                          & "(SELECT TOP  " & px & " PERCENT " & fldname(i) & " FROM " & tablename _
                          & " ORDER BY " & fldname(i) & " ASC);"
110               Set rs = db.OpenRecordset(MySQL)

120               Debug.Print fldname(i) & "  " & px & " Percentile/" & j & " Decile is  " & rs!Percentile
                  
130           Next j
140           Debug.Print vbCrLf
150       Next i
160       rs.Close


170      On Error GoTo 0
180      Exit Sub

decileStuff_Error:

190       MsgBox "Error " & Err.Number & " on line " & Erl & " (" & Err.Description & ") in procedure decileStuff of Module AWF_Related"
End Sub