ms access - SQL nested select and aliases -

ms access - SQL nested select and aliases -

the situation

i have typical ms access database containing info on companies, pay, employees , positions. of tables are:

tbl_report (report_id pk, report_year) tbl_employee (employee_id pk) tbl_pay (pay_id pk, salary, employee_id fk, report_id fk) tbl_position (position_id pk, position, employee_id fk, report_id fk)

i have query selects salary each position , year, produce:

qry_salary_by_position_year: (this query parameterised take 'year'). year | salary | position ------------------------ 2014 | 100 | ceo 2013 | 200 | ceo 2014 | 300 | cfo 2014 | 200 | chairman 2013 | 150 | ceo etc.

i utilize query extract top x percent of salaries given position:

qry_select_top_25: select top 25 percent salary, year, position qry_salary_by_position_year;

which gives like:

salary | year | position ------------------------ 100 | 2014 | ceo 100 | 2014 | cfo 200 | 2014 | cfo

the question

what final table displays max(25%), max(50%), max(75%), max(x%) values, grouped position , year, eg:

year | position | 25th | 50th | 75th ------------------------------------- 2013 | ceo | 10 | 30 | 75 2014 | ceo | 20 | 50 | 80 2014 | cfo | 15 | 30 | 90 2014 | chairman | 20 | 25 | 30

i can 1 percentile value using

select year, position, max(qry50.salary) 50_percentile (select top 50 percent qry_salary_by_position_year.salary, year, position qry_salary_by_position_year) qry50 position in (select distinct position qry_salary_by_position_year) , year in (select distinct year qry_salary_by_position_year) grouping year, position;

but can't head around how build query right aliases etc. add together in other percentage values other columns. have suggestions/comments/questions?

edit

i may have come solution i'm checking:

select qry.year, qry.position, max(qry25.salary) 25_percentile, max(qry50.salary) 50_percentile, max(qry75.salary) 75_percentile, max(qry100.salary) 100_percentile ((((qry_salary_by_position_year qry left outer bring together (select top 50 percent salary, year, position qry_salary_by_position_year) qry50 on qry.year = qry50.year , qry.position = qry50.position) left outer bring together (select top 25 percent salary, year, position qry_salary_by_position_year) qry25 on qry.year = qry25.year , qry.position = qry25.position) left outer bring together (select top 75 percent salary, year, position qry_salary_by_position_year) qry75 on qry.year = qry75.year , qry.position = qry75.position) left outer bring together (select top 100 percent salary, year, position qry_salary_by_position_year) qry100 on qry.year = qry100.year , qry.position = qry100.position) grouping qry.year, qry.position

i think i'm after:

select qry.year, qry.position, max(qry25.salary) 25_percentile, max(qry50.salary) 50_percentile, max(qry75.salary) 75_percentile, max(qry100.salary) 100_percentile ((((qry_salary_by_position_year qry left outer bring together (select top 50 percent salary, year, position qry_salary_by_position_year) qry50 on qry.year = qry50.year , qry.position = qry50.position) left outer bring together (select top 25 percent salary, year, position qry_salary_by_position_year) qry25 on qry.year = qry25.year , qry.position = qry25.position) left outer bring together (select top 75 percent salary, year, position qry_salary_by_position_year) qry75 on qry.year = qry75.year , qry.position = qry75.position) left outer bring together (select top 100 percent salary, year, position qry_salary_by_position_year) qry100 on qry.year = qry100.year , qry.position = qry100.position) grouping qry.year, qry.position

i lead solution reply post: http://stackoverflow.com/a/7855015/4002530

sql ms-access select alias