Calcualte 95th Percentile grouping on one field

geopup
geopup used Ask the Experts™
on
I am a complete novice when it comes to VBA programming.  I have been looking at the different ways to come up with the 95th percentile in Access, but I can't get any of them to work.

I have a table called Fecal_Adjusted with the fields:
Waterbody_ID
Fecal

I need to group on the Waterbody_ID and get the 95th percentile for the Fecal field for each Waterbody_ID.  The waterbody_ID is a text field.

Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
it doesnt make sence to me, can explain more
Even though Access doesn't support percentile calculation, here is a great attempt which you can apply for your case: http://www.mvps.org/access/queries/qry0019.htm.

Author

Commented:
Even though Access doesn't support percentile calculation, here is a great attempt which you can apply for your case: http://www.mvps.org/access/queries/qry0019.htm.

I have already looked at that solution and have no idea how to apply it.  If you could give me step by step on how to use this coding I would appreciate it.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Yadtrt:

Here is an example of the data that I have:

Waterbody ID      Fecal
NV03-BR-16_00      1
NV03-BR-16_00      5
NV03-BR-16_00      10
NV03-BR-16_00      10
NV03-BR-16_00      5
NV03-BR-16_00      5
NV03-BR-16_00      10
NV03-BR-16_00      79

NV03-JR-12_00      10
NV03-JR-12_00      5
NV03-JR-12_00      20
NV03-JR-12_00      170
NV03-JR-12_00      5
NV03-JR-12_00      30
NV03-JR-12_00      10

I need to calculate the 95th percentile in Access for both NV03-BR-16_00 and NV03-JR-12_00 which are 54.85 and 128 respectively (I calculated them in Excel).  They are both in the same table, so I need to group on the the Waterbody_ID to calcualte each one.  I have about 1000 Waterbody_ID's and counting and I receive more Fecal data as the years go by so it is not efficient to calculate them in Excel and apply then in Access.  

Hope this explains it better.

Commented:
Check the attached database, see report 1
DatabaseP.mdb

Author

Commented:
I checked out the database and when I open the report it asked for the percentile value.  Anything that I put in for the value, including nothing, gave me an #error for the percentile.
Commented:
I found a good function that groups here:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_22723540.html

I had to change one part to make it work.

On this line: With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
I had to remove the dbOpenSnapshot and replace it with the number 4 and it worked.  For some reason the doOpenSnapshot remained null and didn't populate like it should.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial