Solved

# Percentile on running total in Access

Posted on 2012-08-26
890 Views
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?
Database1.mdb
0
Question by:Patty01Access

LVL 119

Expert Comment

see the function to get the percentile from this link

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24686688.html
0

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?
0

LVL 119

Expert Comment

How to create a running totals query in Microsoft Access

http://support.microsoft.com/?kbid=290136
0

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.
Database1.mdb
0

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.
0

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.
0

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
Database2.mdb
0

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)
recData.MoveNext
Loop
' rewind
recData.MoveFirst
' 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)
recData.MoveNext
Loop
recData.Close

' 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
``````
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
(°v°)
0

LVL 29

Assisted Solution

Patty,
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.
Database1.mdb
0

LVL 29

Expert Comment

Hey Markus,

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

/Ron
0

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.

(°v°)
0

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!!!!

Patty
0

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...

(°v°)
0

## Featured Post

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 …