Percentile on running total in Access

Posted on 2012-08-26
Last Modified: 2012-08-27
I need to calculate the 40% percentile rent on a table of numbers.  I prefer to do it in Access as I need to produce it on a lot of different areas.

I've attached the mdb file showing my tables.  I want the 40% percentile rent to be determined based on the "cumulative units", or running total.

Below I have the "cumulative" amount shown.   Since there are a total of 553 units, the 40% rent would be the 221st value, which would fall between $715 and $865. I think it would be $767.50??

ID      ONE_RENT      ONE_U      Cumulative
4      $648.00                  16                16
14      $650.00                  26                42
2      $715.00                144               186
13      $865.00                119               305

10      $870.00                  27               332
12      $900.00                  28              360
7      $900.00            23              383
6      $900.00                 19              402
5      $1,005.00         33             435
9      $1,125.00        41             476
1      $1,200.00        28            504
8      $1,275.00        49            553
                  40% percentile = 553*.4=221st  value

Is there a way I could do this in Access please?
Question by:Patty01Access
    LVL 119

    Expert Comment

    by:Rey Obrero
    see the function to get the percentile from this link

    Author Comment

    Thanks capricorn.  Sorry, but I'm not very knowledgeable in the code, and can't translate that code to my problem.

    It seems in that example, there is no "running totals".  I need to first have everything arranged in order, then get the 40% percentile based on the "cumulative" totals, not just the fixed rent numbers.

    Does that make sense?
    LVL 119

    Expert Comment

    by:Rey Obrero
    see this link first

    How to create a running totals query in Microsoft Access
    LVL 29

    Expert Comment

    It's simple to create a report with a cumulative total to come up with the 221st value.  See the revised database I attached.  However, to come up with the 40th percentile rent, I tried following the calculations on this HUD page, but I got lost.  Perhaps you can understand it better and just add textboxes to the bottom of the report with these calculations.

    Author Comment

    Capricorn, I'm still struggling to get the stuff done in the links you gave.  Sorry, but I'm really slow at this and it's very confusing applying those examples to my data.

    Thanks IrogSinta.  You're dead-on. I'm trying to do this calculation for my client who needs HUD rents.  Read your HUD link, and that's clear as mud to me.

    Your report for the running calculations is perfect to arrive at the 40% number.  But can Access take that "221st" value (40%) and do the appropriate pro-rations to arrive at the correct rent number?

    So with my data, the "221st" value falls between $715 (186 cum. total)  and $865 (305 cum. total) rent.  

    $865-$715=$150 difference.

    $865=305 cumulative total.
    $715=186 cumulative total.
               119  difference in cumulative totals

    221 (40% #) -186 cum total (for $715 rent value) = 35 difference

    35/119 difference in cumulative totals = .29

    $150 rent difference x .29 = $43.50 more above $715 rent

    $715+$43.50 =$758.50 as 40% rent??

    Maybe this is really something that needs to be done in Excel not Access?  Put I would really prefer Access.

    Author Comment

    Also, although my data is in Access 2003 format, I'm running Access 2010 (afraid to convert it to 2010 so far).  

    Can Access 2010 maybe do something better?

    I also have Excel 2010 if that's the best place to be doing this.

    Author Comment

    Capricorn, I tried doing the running total query based on your link to the MS site.  But I'm doing something wrong as it keeps giving me ERROR for the running total field.

    The query is in the attached-qryRunTotals.

    Once I get that right, is there a way I can pull in the 40% rent number?

    Thanks, Patty
    LVL 58

    Accepted Solution

    Hello Patty,

    I used your last sample database and created the procedure below. It opens your Query1, loops through the records to obtain a total, rewinds and loops again to find the records before and after the 40% threshold. These four values (two rents and two cumulative percentages) are used to calculate the slope and intercept of the corresponding linear function, which is used in turn to calculate the 40th percentile rent.

    Create a new module, paste the code below into it, make sure that the immediate pane is visible and run the sub. You can later change it to a function, depending on how you intend to use the result.

    Option Compare Database
    Option Explicit
    Sub Calculate40()
        ' Query and field names
        Const cstrQuery = "Query1"
        Const cstrCumulate = "ONE_U"
        Const cstrInterpolate = "ONE_RENT"
        ' variables
        Dim recData         As DAO.Recordset
        Dim lngTotal        As Long
        Dim dblLastPercent  As Double
        Dim dblPrevPercent  As Double
        Dim curLastRent     As Currency
        Dim curPrevRent     As Currency
        Dim dblSlope        As Double
        Dim dblIntercept    As Double
        Dim curRent         As Currency
        ' open sorted query
        Set recData = CurrentDb.OpenRecordset(cstrQuery)
        ' get total of cumulative field
        Do Until recData.EOF
            lngTotal = lngTotal + recData(cstrCumulate)
        ' rewind
        ' get values below and above 40%
        Do Until dblLastPercent > 0.4
            ' store previous values
            dblPrevPercent = dblLastPercent
            curPrevRent = curLastRent
            ' store current values
            dblLastPercent = dblLastPercent + recData(cstrCumulate) / lngTotal
            curLastRent = recData(cstrInterpolate)
        ' calculate interpolation constants
        dblSlope = (dblLastPercent - dblPrevPercent) / (curLastRent - curPrevRent)
        dblIntercept = curLastRent * dblSlope - dblLastPercent
        ' calculate 40th percentile rent
        curRent = (0.4 + dblIntercept) / dblSlope
        ' display variables and final result
        Debug.Print dblPrevPercent, dblLastPercent, lngTotal, curPrevRent, curLastRent
        Debug.Print dblSlope, dblIntercept
        Debug.Print curRent
    End Sub

    Open in new window

    If you prefer, you can use other intermediate variables to mimic your calculations in http:#a38334445 more closely. This might be a good idea, as my results seem to differ from yours slightly, and I haven't tried to find the exact reason.

    I hope this gets you started
    LVL 29

    Assisted Solution

    I revised my report and it now shows $759.12 as the 40% rent.  I'm not sure why it shows in the Print Preview and not in the Report View though.
    LVL 29

    Expert Comment

    Hey Markus,

    I just saw your code.  I have to say that it looks well organized.

    LVL 58

    Expert Comment

    Hi, Ron.

    I tend to code like that even for myself (minus the obvious comments). It's good that you revised your report to include the calculation: it shows that there are many ways to code the same idea and many places where the code can be included.

    Note: the Format event isn't triggered in report view. There is the Paint event instead, but using it has strange side effects, which I have never explored completely.


    Author Comment

    Ron and Markus, you both ROCK!!!! No wonder you both are Access Geniuses!!!

    It's an enormous help to people like me with very limited programming experience in Access, when you present things very simply.

    Ron, your report made it easy for me to understand what you were doing.  Thank you!!  Although I don't understand why the code is on the "On Format" event.  And I'm unsure what the Paint event is that Markus referred to.  But it works, and that's all I care about!

    Markus, your additional comments in the code were extremely valuable to help me understand what was going on.  Thank you for taking the extra time to do that.

    I had a very convoluted way of doing this in Excel that took forever.  You have both reduced that job for me enormously.

    Thank you, thank you, thank you!!!!

    LVL 58

    Expert Comment

    And thank you for the feedback! This is definitely one of the things that keep me answering questions on Experts Exchange...

    Success with your project!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now