# Thread: Quartile and Percentile Calculations

1. Registered User
Join Date
Jan 2003
Posts
4

## Unanswered: Quartile and Percentile Calculations

Does anyone know of a way that I can do this for data held in Access XP. (Excel uses @quartile and @percentile.)

2. Registered User
Join Date
Aug 2002
Location
Charlotte NC
Posts
665
This is how I do it. Basicaly you will need two queries if you need to create an other catagorie, wich i would so that you have a complete picture.

The first query gets the object (ie country) and the sum of the measuers (ie revenue) and then the precentage that that country had of the total using a formula similar to :
(Sum(Table_PCRKMS_Local_Data.FFE)/DLookup('sum(ffe)','Table_PCRKMS_Local_Data'))*100

The formula basicaly says total revenue for that country devided by the total revenue of all records multiplied by 100. This will give you the perectage of that single record

to get the other grouping use the second query:

Select iif([Percent] > 1.5, [Port_Pairs],'OTHER*'),sum([FFE])
from graph_FFE
group by iif([Percent] > 1.5, [Port_Pairs],'OTHER*')
order by 2 desc

This basicaly says if the perecentage is greater than 1.5 then use the country else other and them sum the revenue. So with two queries you can have your percentages

Regards
Jim

Dim Cur_DB As DAO.Database
Dim Record_Set As DAO.QueryDef
Set Cur_DB = CurrentDb()

' delete qrydef if it exists
For Each qdf In Cur_DB.QueryDefs
If qdf.Name = "graph_FFE" Then
Cur_DB.QueryDefs.Delete "graph_FFE"
Exit For
End If
Next qdf

Country_To_Country.RowSourceType = "Table/Query"
Set Record_Set = Cur_DB.CreateQueryDef("graph_FFE", "SELECT [Load] & ' - ' & [Discharge] AS port_Pairs" & _
", Sum(Table_PCRKMS_Local_Data.FFE) AS FFE ," & _
" (Sum(Table_PCRKMS_Local_Data.FFE)/DLookup('FFE','Table:_PCRKMS_METRICS_SUM'))*100 as [Percent]" & _
" FROM Table_PCRKMS_Local_Data" & _
" GROUP BY [Load] & ' - ' & [Discharge]" & _
" ORDER BY Sum(Table_PCRKMS_Local_Data.FFE) DESC;")
Country_To_Country.RowSource = "Select iif([Percent] > 1.5, [Port_Pairs],'OTHER*'),sum([FFE]) from graph_FFE group by iif([Percent] > 1.5, [Port_Pairs],'OTHER*') order by 2 desc;"

3. Registered User
Join Date
Jan 2003
Posts
4
Tx Jim for the response.

I think you have given me code to calculate percentage, rather than percentile. (The latter is the percentage position up a rank order of values - so 50th percentile is half-way up.)

Regards,

4. Registered User
Join Date
Aug 2002
Location
Charlotte NC
Posts
665
Originally posted by robin.jordan
Tx Jim for the response.

I think you have given me code to calculate percentage, rather than percentile. (The latter is the percentage position up a rank order of values - so 50th percentile is half-way up.)

Regards,
ya your right sorry about that, but maybe you can use it as a modle, seems to me you will have to do two passes, first to rank and second to filter out the part that you want, maybe you can gather your metrics in the first pass along with some type of auto number sorting the query descending by the metric and then take the second pass to get the top 50 auto numbers......maybe........just a guess at this point
Jim

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

6. Registered User
Join Date
Jan 2003
Posts
4
Thanks Rudi, that got me started. A search on that website (which I had not come across before) actually gave me another link to some actual Access code to calculate quartiles. Problem solved.

Thanks to all

7. Registered User
Join Date
Jan 2003
Posts
4
Jim,

Your original code set me thinking if I ordered the values, calculated a value of their position and then compared it to the percentile value I'd be along the right lines (except I would not know how to code it). Luckily Rudi gave me a url to get me started - see other message). On that site I also found a link to

http://www.fabalou.com/Access/Modules/percentile.htm

This has some actual Access code that I can cut and paste in.

Regards

8. Registered User
Join Date
Aug 2002
Location
Charlotte NC
Posts
665
Originally posted by robin.jordan
Jim,

Your original code set me thinking if I ordered the values, calculated a value of their position and then compared it to the percentile value I'd be along the right lines (except I would not know how to code it). Luckily Rudi gave me a url to get me started - see other message). On that site I also found a link to

http://www.fabalou.com/Access/Modules/percentile.htm

This has some actual Access code that I can cut and paste in.

Regards
Cool...maybe if we all start to think we will be able to come up with a solution...
Jim

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•