G
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
PERCENTILE(array,k)
Pat
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
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
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
G
Gary Walter
Excel function but does not work in Access:Pat Diminico said:What is the equal function in an Access query to calculate percentile. This is the
Hi Pat,PERCENTILE(array,k)
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
T
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
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
calculate percentile. This is the-----Original Message-----
G
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
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
tansh said: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.
Tancalculate percentile. This is the-----Original Message-----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
.