Tags: access, calculations, database, excel, microsoft, ms-access, mysql, oracle, percentile, quartile, sql, todaysummary

Quartile and Percentile Calculations

On Database » Microsoft Access

4,979 words with 7 Comments; publish: Sat, 01 Dec 2007 03:22:00 GMT; (25046.88, « »)

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

Tx in advance.

All Comments

Leave a comment...

  • 7 Comments
    • 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;"

      #1; Tue, 11 Dec 2007 19:30:00 GMT
    • 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,

      #2; Tue, 11 Dec 2007 19:31:00 GMT
    • === Original Words ===

      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

      #3; Tue, 11 Dec 2007 19:32:00 GMT
    • see Calculating Percentiles in SQL (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid450343_tax289340,00.html)

      rudy

      http://rudy.ca/

      #4; Tue, 11 Dec 2007 19:33:00 GMT
    • 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

      #5; Tue, 11 Dec 2007 19:34:00 GMT
    • 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

      #6; Tue, 11 Dec 2007 19:35:00 GMT
    • === Original Words ===

      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

      #7; Tue, 11 Dec 2007 19:36:00 GMT