Ranking

[tamer64]

I need some help on ranking and hoping someone can assist. I have an EmployeeID field "EmpID" which is a number field and an Hours field which is a Text Field. I would like to create a ranking column to rank the the employee with the highest amount of hours. Is this possible to do in a query?

EmpID Hours
17 18:00
42 7:00
47 25:00
57 9:00
58 14:00
59 8:00
108 21:00
110 4:00
111 32:00
113 00:00
139 34:30
140 16:30
152 32:30
155 16:00
157 10:10

Desired output...

EmpID Hours Rank
139 34:30 1
152 32:30 2
111 32:00 3
47 25:00 4
108 21:00 5
17 18:00 6
140 16:30 7
155 16:00 8
58 14:00 9
157 10:10 10
57 9:00 11
59 8:00 12
42 7:00 13
110 4:00 14
113 00:00 15

 

[SkipVought]

hi,

EmployeeID field "EmpID" which is a number field and an Hours field which is a Text Field

How backwards can you go?

Do you plan to do arithmetic on your EmployeeID field? What does it mean to divide an EmployeeID by 2? An EmployeeID is an IDENTIFIER. Ususlly the LENGTH of an identifier field is fixed and leading zeros are significant. Your EmployeeID field ought to be TEXT!

Your text Hours field is useless for calculating duration or for collating! Your Hours field ought to be NUMERIC!

Whoever designed your table is the one who needs help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

What about this ?

SELECT A.EmpID,A.Hours,Count(*) AS Rank
FROM tblEmpHours A,tblEmpHours B
WHERE Val(Left(A.Hours,Len(A.Hours)-3) & Right(A.Hours,2))<=Val(Left(B.Hours,Len(B.Hours)-3) & Right(B.Hours,2))
GROUP BY A.EmpID,A.Hours
ORDER BY 3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tamer64]

Thank You PHV. Let me give this a try.