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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 982

# 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?
Database1.mdb
0
Patty01Access
• 5
• 3
• 3
• +1
2 Solutions

Commented:
see the function to get the percentile from this link

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

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

Commented:

How to create a running totals query in Microsoft Access

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

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

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

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

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

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

Commented:
Hey Markus,

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

/Ron
0

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

Patty
0

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