The problem with a code is because it calculate a percentile value for the whole data set. I need values for the each group, that are grouped by the same name or number of other field.
I have to much groups that I can't make other queries to calculate from them
I don't know if I explain it well my problem
my code is:
Public Function Percentile(fldName As String, _
tblName As String, p As Double, _
Optional strWHERE As String = "") _
As Double
'Note tblName can also be the name of a query or view
'ADO
'Dim cnn As ADODB.Connection
'Dim rst As ADODB.Recordset
'DAO
Dim Cdb As Database
Dim rst As Recordset
'Other Vars
Dim break_pt As Double
Dim sqlSort As String
Dim low_obs As Long, high_obs As Long
Dim r1 As Double, r2 As Double, x As Double
Dim N As Long
Dim recno As Long
'VERIFY VALID PERCENTILE (0-100) WAS GIVEN
If (p <= 0 Or p >= 100) Then
Percentile = -555555555 'Something to stick out!
Exit Function
End If
'ENSURE DESIRED DATA IS SORTED
If Len(strWHERE) < 1 Then
sqlSort = "SELECT [" & fldName & "] " & _
"FROM [" & tblName & "] " & _
"ORDER BY [" & fldName & "]"
Else
sqlSort = "SELECT [" & fldName & "] " & _
"FROM [" & tblName & "] WHERE " & _
strWHERE & _
" ORDER BY [" & fldName & "]"
End If
'ADO
'Set cnn = CurrentProject.Connection
'Set rst = New ADODB.Recordset
'rst.Open sqlSort, cnn, adOpenStatic, adLockReadOnly, 1
'DAO
Set Cdb = CurrentDb()
Set rst = Cdb.OpenRecordset(sqlSort, dbOpenSnapshot)
rst.MoveLast
'How many observatons? For example, N=12
N = rst.RecordCount
'Which observation would, theoretically, _
be the pTH "true" percentile.
'e.g., for 25th percentile would be _
the 0.25*(12+1)=3.25th observation
break_pt = (p / 100) * (N + 1) '3.25 = (25/100)*(12+1)
'There's 2 special extreme cases we need to worry about!
'small sample for small percentile
If break_pt < 1 Then break_pt = 1
'small sample for large percentile
If break_pt > N Then break_pt = N
'But since there's no such thing as a _
3.25th observation, we estimate it _
'somewhere between the 3rd and 4th observations. _
It'll be approximately: p = r1*low_obs + r2*high_obs
low_obs = Int(break_pt) '3 = int(3.25)
high_obs = low_obs + 1 '4 = 3 + 1
'Now, we have to interprolate between the "boundaries"
r1 = high_obs - break_pt '0.75 = 4 - 3.25
r2 = 1 - r1 '0.25 = 1 - 0.75
'Since we have determined the needed _
observations and their weights we can move to the _
projected low observation and loop _
through the record set until we reach the _
high observation to calc the resulting percentile
'DAO, where absolution position is 0 based
rst.AbsolutePosition = low_obs - 1: recno = low_obs - 1
'ADO where absolution position is 1 based
'rst.AbsolutePosition = low_obs: recno = low_obs - 1
DoEvents
Do Until rst.EOF
recno = recno + 1
If recno = low_obs Then x = r1 * rst(0)
If recno = high_obs Then
x = x + r2 * rst(0)
Exit Do
End If
rst.MoveNext
Loop
'We now have our percentile!
Percentile = x
rst.Close
Set rst = Nothing
'DAO
Set Cdb = Nothing
'ADO
'Set Conn = Nothing
End Function
'Public Sub Test()
'Dim strMsg As String
' strMsg = "25th = " & Percentile("YourField", "YourTable", 25) _
' & vbNewLine & _
' "Median = " & Percentile("YourField", "YourTable", 50) _
' & vbNewLine & _
' "75th = " & Percentile("YourField", "YourField", 75)
'MsgBox strMsg
'End Sub
also I don't know how to use last function strWHERE- maybe is a solution of my problem
anyone?
