Percentile function in a Access Query

[Guest]

What is the equal function in an Access query to calculate percentile. This is the Excel function but does not work in Access:

PERCENTILE(array,k)

Pat

 

[GreySky]

You might try mixing it up with TOP x% queries.

All Air Queries:

25% percentile:
SELECT Last([MyField])
FROM (SELECT TOP 25% [MyField] FROM MyTable
ORDER BY [MyField])

75% percentile
SELECT Last([MyField])
FROM (SELECT TOP 25% [MyField] FROM MyTable
ORDER BY [MyField] DESC)

David Atkins, MCP

 

[Gary Walter]

What is the equal function in an Access query to calculate percentile. This is the

Excel function but does not work in Access:

PERCENTILE(array,k)

Hi Pat,

On the MVP org site Michel breaks
the definition of Percentile down
into DMin and DCounts, then provides
a function using them that you can save in
a code module, then use in your query.

http://www.mvps.org/access/queries/qry0019.htm

Good luck,

Gary Walter

 

[tansh]

Hi,

Can I check if the solution works for you? I have tried to
to implement it but I keep running into this error:

Run-time error '3075':
Syntax error in query expression 'DCount
("*", "Value", "[UL Value]<=" & [[UL Value]]) >= 818.73'

The table name is "Value" while the Field Name is "UL
Value"

Please help as I need a solution ASAP. Thanks.

Tan

-----Original Message-----

calculate percentile. This is the

 

[Gary Walter]

Hi Tan,

First..I assume you are just testing
and would never use a reserved word
"Value" for a table name.

But I do not believe that is the problem....

I believe it is choking on the double brackets
around UL Value.

Change your field name to "UL_Value"
and don't use brackets in the function call

XPercentile("UL_Value","Value",.25)

or eliminate the brackets in the function

'*** code w/o brackets ****
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & " & FName & " ) >= " & _
X * DCount("*", TName))
End Function
'*** end code w/o brackets ***

XPercentile("[UL Value]","Value",.25)

Please respond back if I have misunderstood.

Good luck,

Gary Walter

Hi,

Can I check if the solution works for you? I have tried to
to implement it but I keep running into this error:

Run-time error '3075':
Syntax error in query expression 'DCount
("*", "Value", "[UL Value]<=" & [[UL Value]]) >= 818.73'

The table name is "Value" while the Field Name is "UL
Value"

Please help as I need a solution ASAP. Thanks.

Tan

-----Original Message-----

calculate percentile. This is the

Excel function but does not work in Access:
Hi Pat,

On the MVP org site Michel breaks
the definition of Percentile down
into DMin and DCounts, then provides
a function using them that you can save in
a code module, then use in your query.

http://www.mvps.org/access/queries/qry0019.htm

Good luck,

Gary Walter


.