Percetile bucketing

[agentmcmillan]

I have found this page

http://bytes.com/topic/access/answer...rcentile-usage

I have the percentile function working but instead of calculating a percentile for each bucket it is calculating if for the whole data set.I need it per bucket. Any help is greatly appreciated

Name    Days
bob    5
bob    3
bob    3
bob    56
bob    76
bob    3
bob    56
bob    8
bob    7
jake    34
jake    3
jake    2
jake    6
jake    8
jake    12
jake    32
jake    7
ted    87
ted    9
ted    3
ted    12
ted    12
ted    3
ted    5
walt    45
walt    8
walt    67
walt    67
walt    97
walt    23
walt    41
walt    45
walt    12
walt    12
walt    15
walt    16
 

Sorry for the spelling error. I am unable to change the title.

[FishVal]

fCalculatePercentiles() function written by ADezii takes all records from the table. You need to modify it in such a way that it filters only relevant records. It is not that difficult. Please post what you've tried yourself so far and you will certainly get help on where you've messed.

Kind regards,
Fish

[agentmcmillan]

@FishVal
The Challenge I am running into is that I don’t know where to start. Access may be a 2nd nature to you, but I am very new and frustrated with the software. I typically only work in excel. The table I posted is a very low level example of what I am doing. I am looking at a phone center that in within my department and trying to derive our 90th percentile of call times for the call types we receive. The VBA module that is used it out of my skill set. I greatly appreciate any help you can give me.

Thanks again
AgentMcMillan

[FishVal]

@agentmcmillan
You need to pass an additional argument to fCalculatePercentiles() - a value of [bucket] field to filter the table records by. fCalculatePercentiles() code should filter relevant records when opening recordset.

[agentmcmillan]

@FishVal
are you saying something along the lines of a new dim value or when the table is being moved over define that you want the bucket?

[agentmcmillan]

Here is what I came up with this afternoon

Public Function fCalculatePercentiles(strName As String, sngPercentile As Single)
 
'Make sure to 1st set a Reference to the
 
'Microsoft Excel XX.X Object Library
 
Dim sngNumbers() As Single
 
Dim intNumberOfRecords As Integer
 
Dim objExcel As Excel.Application
 
Dim intCounter As Integer
 
Dim MyDB As DAO.Database
 
Dim rstPercentile As DAO.Recordset
 
 
 
Set MyDB = CurrentDb()
 
Set rstPercentile = MyDB.OpenRecordset("Select * from TBL_DATA WHERE [Name] = '" & strName & "'", dbOpenSnapshot)
 
rstPercentile.MoveLast: rstPercentile.MoveFirst     'accurate Record Count
 
 
 
intNumberOfRecords = rstPercentile.RecordCount
 
 
 
'Redimension the Array to proper values
 
ReDim sngNumbers(1 To intNumberOfRecords)
 
 
 
Set objExcel = CreateObject("Excel.Application")
 
 
 
'Populate the Array with values from the Recordset
 
For intCounter = 1 To intNumberOfRecords
 
  With rstPercentile
 
    sngNumbers(intCounter) = ![Days]
 
    .MoveNext
 
  End With
 
Next
 
 
 
'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
 
fCalculatePercentiles = Round(objExcel.Application.Percentile(sngNumbers(), sngPercentile), 2)
 
 
 
'Don't forget to clean up!!!
 
rstPercentile.Close
 
Set rstPercentile = Nothing
 
objExcel.Quit
 
Set objExcel = Nothing
 
End Function