Results 1 to 14 of 14
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: Microsoft Access 2007 Percentile Calculation

    After days of trying to create a percentile calculation in access i have decided to ask you for help.



    i have a table with the following columns in access



    Transaction Date
    Days until Returns
    User ID
    Method
    Trans ID
    Return Date
    ReturnReason



    I would like to calculate the percentile for a perticular return reason per month by using the Days until Returns column as the interger.



    Is their any adivice or sql you can provide which can help me?



    Thanking you in advance.



    Sean

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "I would like to calculate the percentile for a perticular return reason per month by using the Days until Returns column as the interger."

    Don't quite understand you hear;

    You want to show a % for each 'ReturnReason' - do you mean if you had
    say 5 people on one category, 5 in another and 40 in a third it would return:

    category one 10%
    category two 10%
    category three 80%

    If not, please post sample data and output required.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    er...Percentile ! (blush)

    In that case - sample data and desired output definately please.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    May 2009
    Posts
    17

    Microsoft Access 2007 Percentile Calculation

    Gareth,

    thanks for your response - apologise on the confusion hope this clarifies........

    if i return reason of "Account closed" which has 8 enteries in the days until return columns

    i.e

    Return reason Days until returns
    Account Closed 21
    Account Closed 2
    Account Closed 1
    Account Closed 34
    Account Closed 12
    Account Closed 18
    Account Closed 12
    Account Closed 46


    I would like to know the 95th percentile so in the results table of the query i would see a single entry for Account closed with the value for 95th percentile.

    in excel all i would do is select all 8 enteries underdays to return and place in percentile function if i used the above 8 numbers in excel and am looking for the 95th percentile the answer would be 41.8

    i.e
    =PERCENTILE("8 numbers",0.95)

    any help?

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    hang fire...drum roll...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    May 2009
    Posts
    17
    please say you have the answer...................?

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Just looking at it now - bet Rudy could write a killer bit of SQL at this point!...

    I'm pretty sure this could be done elegantly with subqueries and certainly as a VBA function.

    Please be patient - I may not be able to respond immediately!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    May 2009
    Posts
    17
    Hey Gareth,

    Found this code on internet - not sure if its any help- does it make any sense?

    Public Function Percentile(strTbl As String, strFld As String, k As Double)
    As Double
    Dim rst As ADODB.Recordset
    Dim dblData() As Double
    Dim xl As Object
    Dim x As Integer
    Set xl = CreateObject("Excel.Application")
    Set rst = New ADODB.Recordset
    rst.Open "Select * from " & strTbl, CurrentProject.Connection,
    adOpenStatic
    ReDim dblData(rst.RecordCount - 1)
    For x = 0 To (rst.RecordCount - 1)
    dblData(x) = rst(strFld)
    rst.MoveNext
    Next x
    Percentile = xl.WorksheetFunction.Percentile(dblData, k)
    rst.Close
    Set rst = Nothing
    Set xl = Nothing
    End Function

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hi Sachin,

    Yes - this code just automates excel to get the percentile via the application. (Essentially, Excel 'works it out').

    However, what I was looking to do was to provide an answer either as;

    An SQL statement/s
    Or using a VBA function (Without Automating Excel).

    <both of which will require a little time (I need to work out how precicely Excel calculates percentiles).

    I will post a solution for you but I cannot say exactly when - Best guess is Monday evening given my current workload.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    May 2009
    Posts
    17
    Gareth,

    your a star - thanks and i look forward to seeing the result query.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by garethdart
    bet Rudy could write a killer bit of SQL
    Code:
    SELECT (MAX([days until returns]) - MIN([days until returns])) * 0.95 AS we_dont_needs_no_rude_boi
    FROM myTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2009
    Posts
    17
    Rudy,

    Thanks for your response, however that calculation gives us a valueof 42.75 and not the 41.8 that excel gives on the percentile function???

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No you are right - wrong formula. I misread the data, and thought the lowest value was "2", which funnily enough would return the right value.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ Thanks Rudy
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •