Naughty Access query

Mr Robin

New Member
Joined
Jan 14, 2004
Messages
22
I know this is an Excel board and I'm sorry but I hoping that someone can still help.
Any ideas how I can right a query that will just select all values in a table apart from the lowest and highest 5% of values for a certain field.
For example, suppose the table contained salary details, how could I ignore the lowest paid 5% and the highest paid 5%.

Thanks in advance.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
I did this using three queries, although you could probably do it in one with a subquery. The table I created had two fields-

Name (Primary Key)
Salary

The first query I saved as Bottom95, the SQL was-

SELECT TOP 95 PERCENT Table3.Salary, Table3.Name
FROM Table3
ORDER BY Table3.Salary;

The second was called Top95, the SQL was -

SELECT TOP 95 PERCENT Table3.Salary, Table3.Name
FROM Table3
ORDER BY Table3.Salary DESC;

Finally, I added both queries to a new query and used this SQL-

SELECT Bottom95.Salary
FROM Bottom95 INNER JOIN Top95 ON Bottom95.Name = Top95.Name;

BTW, there is an Access forum on the board, this will probably be moved there.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,535
Messages
5,596,741
Members
414,094
Latest member
dingo_baby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top