[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

Percentile on running total in Access

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?
  • 5
  • 3
  • 3
  • +1
2 Solutions
Rey Obrero (Capricorn1)Commented:
see the function to get the percentile from this link

Patty01AccessAuthor Commented:
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?
Rey Obrero (Capricorn1)Commented:
see this link first

How to create a running totals query in Microsoft Access

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Patty01AccessAuthor Commented:
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.
Patty01AccessAuthor Commented:
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.
Patty01AccessAuthor Commented:
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
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
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.
Hey Markus,

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

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.

Patty01AccessAuthor Commented:
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!!!!

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!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now