Filter out Bottom 5% and Top 5% Percentile

[akira]

Hi all,

Just wondering if it's possible to filter out the bottom and top 5% of a value in a table. For example,

Group Value
A 1111
A 223
A 423
A 241


A 142
A 132
A 1
B
.
.
.

As you can see in the dummy data, Value 1111 and value 1 are the outliers which may affect my future calculations. Is there any way that when I set up my query, it will filter those outliers out?

Thanks,
akira

[June7]

This requires calculating 95 and 5 percentiles and then filter records with values outside that range. Never done this and research indicates not easy. Review:
http://access.mvps.org/access/queries/qry0019.htm
http://www.fmsinc.com/MicrosoftAcces...s/percentiles/

This offered hope for a simple query solution but never quite resolved:
http://www.dbforums.com/microsoft-ac...lculation.html

Then I find this:
http://blogannath.blogspot.com/2010/...entile_25.html

And tested this:
SELECT Group, [Value]
FROM Pct95 RIGHT JOIN (Table1 LEFT JOIN Pct5 ON Table1.[Amount] = Pct5.[Amount]) ON Pct95.Amount = Table1.Amount
WHERE (((Pct5.Amount) Is Null) AND ((Pct95.Amount) Is Null));

There is only one record in each of the 5 and 95 percentiles and don't think would work if there were more.

So tried this which looks like best I can do:
SELECT Group, [Value]
FROM Table1
WHERE Table1.[Value] Not IN(SELECT TOP 5 PERCENT Table1.[Value] FROM Table1 ORDER BY Table1.[Value])
AND Table1.[Value] Not IN(SELECT TOP 5 PERCENT Table1.[Value] FROM Table1 ORDER BY Table1.[Value] DESC);

BTW, Value is a reserved word and should not use reserved words as names. Amount or Quantity would be better.