Access SQL querry to rank employees

[samuelochieng]

i have this table and i want an access SQL to rank the employees according to the salary they earn.

b1​

dept​

name​

salary​

10
hellen

300​

20
ken

200​

20
mike

800​

10
sam

200​

10
susan

200​

20
walter

100​

this should be the result of that querry

pos​

dept​

name​

salary​

rank​

10
hellen

300​

1​

10
sam

200​

2​

10
susan

200​

2​

20
mike

800​

1​

20
ken

200​

2​

20
walter

100​

3​

 

[jdraw]

i have this table and i want an access SQL to rank the employees according to the salary they earn.

b1
dept
name
salary​

10
hellen

300​

20
ken

200​

20
mike

800​

10
sam

200​

10
susan

200​

20
walter

100​

this should be the result of that querry

pos
dept
name
salary
rank​

10
hellen

300
1​

10
sam

200
2​

10
susan

200
2​

20
mike

800
1​

20
ken

200
2​

20
walter

100
3​

Access SQL does not have a built in RANK function.
here's a link to some Access SQL.
http://msdn.microsoft.com/en-us/library/aa140015.aspx

 

[jal]

If you only wanted a company-wide salary ranking (i.e. regarding the whole company as one huge dept), I should think this would be very do-able even in pure sql.

But what you really are after, according to your post, is multiple rankings. Each dept is to rank its members according to the salaries within that dept alone. That's a tough one for a beginner like me. I think you'll want to invoke the aid of a VBA loop to do it.

Anyway, if you change your mind, that is, if you decide you can get by with a company-wide ranking, you can do this in three queries. The first query creates a temporary table.

(1)
Select Salary into ListOfSalaries from B1 Group By Salary
Order By Salary DESC

(2) Alter Table ListOfSalaries Add column Ranking Counter

(3) Select L.Ranking, B1.* Into Rankings From B1
Inner Join ListOfSalaries as L on L.Salary = B1.Salary

 

[samuelochieng]

multiple by department ranking

Thanks for your suggestion but i want multiple ranking by department. This should just be an access sql querry.

 

[jal]

Thanks for your suggestion but i want multiple ranking by department. This should just be an access sql querry.

I think I got this working. I renamed your main table from B1 to employees and then did the following five queries:

SELECT deptid, salary, 0 AS Ranking
INTO ListOfSalaries FROM Employees GROUP BY deptID, salary
ORDER BY deptId, salary DEsc

ALTER TABLE ListOfSalaries ADD COLUMN PseudoRanking Counter


SELECT DeptID, -MIN(PseudoRanking) AS OffSETForThisDept INTO OffSETs FROM ListOfSalaries GROUP BY DeptID


UPDATE ListOfSalaries AS L
INNER JOIN offSETs AS O ON o.DeptID = L.DeptID
SET Ranking = L.Pseudoranking + ffSETForThisDept + 1


SELECT E.*, L.Ranking INTO Rankings FROM Employees AS E
INNER JOIN ListOfSalaries AS L ON L.DeptID = E.DeptId AND L.Salary = E.Salary


That was a lot of time and work!

 

[jal]

BTW, here's how it works. The pesuoRanking column is an autonumber column so there is kind of a sense in which it is doing a company-wide ranking. Thus you could have, for example:

deptid salary PseudoRanking
10 800 33
10 700 34

I just needed to convert the 33 and 34 to numbers 1 and 2 as to get a dept-wide ranking instead of a company-wide ranking:

deptid salary PseudoRanking Ranking
10 800 33 1
10 700 34 2


To do this, I regard the Min of the two values (which is 33) as the "offset".
So the ranking values are just
33 - offset + 1 = 1
34 - offset + 1 = 2
because the offset amount for this dept is 33. (Each dept has its own offset amt which is just the MIn(PseudoRanking) for that dept).

 

[javi-e]

Absolute genius! Jal, thanks for posting this. Worked beautifully for me and saved me a lot of head aches!!

 

[robherms]

This works beautifully! Wish I found it about 2 days ago!!