percentile/quartile function

[Guest]

I need to calculate 0.25 and 0.75 percentile in a query grouping by different
columns.
Is there any module/function available to call from a query?
I have searched on the net and found some functions, but they calculate the
percentile for whole field, in a given table.
But I need to group the values based on different criteria and calculate the
percentiles for each group which will result different percentile values for
different groups.
I paste part of my data below with a comma separated format. I need to
calculate percentiles for each ID, stimulation and week.


Id,Stimulation,Replication,Week,Value
RCl,SEB,Rep1,preblee,49604
RCl,SEB,Rep2,preblee,43826
RCl,SEB,Rep3,preblee,37487
RCl,SEB,Rep4,preblee,41949
RCl,NS,Rep1,preblee,1653
RCl,NS,Rep2,preblee,1204
RCl,NS,Rep3,preblee,18104
RCl,NS,Rep4,preblee,2209
RCl,Gag,Rep1,preblee,258
RCl,Gag,Rep2,preblee,235
RCl,Gag,Rep3,preblee,2325
RCl,Gag,Rep4,preblee,401
RCl,Env1,Rep1,preblee,217
RCl,Env1,Rep2,preblee,933
RCl,Env1,Rep3,preblee,383
RCl,Env1,Rep4,preblee,2468
RCl,Env2,Rep1,preblee,309
RCl,Env2,Rep2,preblee,297
RCl,Env2,Rep3,preblee,4596
RCl,Env2,Rep4,preblee,1516

 

[Guest]

I am not sure I understand exactly what you want to do; however, you can use
most functions in a query and you can write user defined queries that return
a value the query can understand.
If I understand correctly, you want to group the items like this:

RCl,SEB,Rep1,preblee,49604
RCl,SEB,Rep2,preblee,43826
RCl,SEB,Rep3,preblee,37487
RCl,SEB,Rep4,preblee,41949

The sum of Value is 172866 and you want to calculate the precentiles on that
number.

If this is correct, then you need to create a query that will exclude
replication. It needs to be a Totals query with ID, Stimulation, and preblee
as Group By. You then need a calculated field:
Percentile: Value * IIf(SomeCondition, .25, .75)
In the query Total row, select Sum

What you did not included, is how you know what the multiplier is selected.
You need to put that logic where I have SomeCondition

Where all the ID's are RCI, All the Stimulations are SEB, and all the Weeks
are preblee

 

[Guest]

Actually I need to calculate 25th percentile and 75th percentiles. not the
perecentages.
Excel has percentile functions to do this like ERCENTILE(array,k).
I am looking for similar type of functions in access.

 

[Guest]

Sorry, there is no such function in Access.

Actually I need to calculate 25th percentile and 75th percentiles. not the
perecentages.
Excel has percentile functions to do this like ERCENTILE(array,k).
I am looking for similar type of functions in access.

 

[Tim Ferguson]

I need to calculate 0.25 and 0.75 percentile in a query grouping by
different columns.

I think this is possible using subqueries with a TOP 25 PERCENT clause,
although I don't have time to experiment just now.

Try googling on "SQL Percentiles" and the top three results all provide
useful methods.

Hope that helps


Tim F