Percetile bucketing

[agentmcmillan]

I have found this page

http://bytes.com/topic/access/answers/869765-percentile-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]

fCalculatePerce ntiles() 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]

fCalculatePerce ntiles() 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

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]

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

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

[agentmcmillan]

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

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