Generic query to find the median

[SunWuKung]

I have a table with one column (for the matter of simplicity).
I am looking for the number where X percent of the numbers in that column are smaller than that number. When X=50 the number I should get is the median, but that is just an example I would need to find a generic solution to this problem.

Thanks for the help.
SWK

 

[SunWuKung]

I think I have it now.
The syntax here is for SQL server but the logic will be the same for Access.

Set @P=0.01

SELECT
x.CandidateScaleValue
FROM ScaleData x CROSS JOIN ScaleData y
GROUP BY x.CandidateScaleValue
HAVING
SUM(CASE WHEN y.CandidateScaleValue <= x.CandidateScaleValue THEN 1 ELSE 0 END)>=(COUNT(*)+1)*@P AND
SUM(CASE WHEN y.CandidateScaleValue >= x.CandidateScaleValue THEN 1 ELSE 0 END)>=(COUNT(*)*(1-@P))+1

 

[SunWuKung]

The previous one did not always returned a result.
This one does.

SELECT TOP 1
x.CandidateScaleValue
FROM ScaleData x CROSS JOIN ScaleData y
GROUP BY x.CandidateScaleValue
HAVING
SUM(CASE WHEN y.CandidateScaleValue <= x.CandidateScaleValue THEN 1 ELSE 0 END)>=(COUNT(*)+1)*@P
ORDER BY
SUM(CASE WHEN y.CandidateScaleValue <= x.CandidateScaleValue THEN 1 ELSE 0 END)