Ranking (1 Viewer)

[avisccs]

I searched the forum, but can't found any which is similar.

I got this data set (TB1):
DeptA Date1 Staff1
DeptA Date1 Staff2
DeptA Date2 Staff3
DeptA Date3 Staff4
DeptA Date3 Staff5
DeptB Date1 Staff6
DeptB Date1 Staff7
DeptB Date2 Staff8
*Note:
1. Date is the start date of the staff. Thus, more than 1 staff can start on the same day.
2. Staff is unique.
3. Staff number was not assigned in order of date. Thus Staff 1 can start later than staff 3, but staff1 might have got the smaller staff number.
4. currently the whole table is not sorted, but I can sort it if needed.

I want to rank it to be:
DeptA Date1 Staff1 1
DeptA Date2 Staff2 2
DeptA Date2 Staff3 1
DeptA Date3 Staff4 1
DeptA Date4 Staff5 2
DeptB Date1 Staff6 1
DeptB Date1 Staff7 2
DeptB Date3 Staff8 1


I tried:
SELECT iif(Dept=T.Dept and DateS<>T.DateS,(SELECT count(*) from TB1 where DateS <= T.DateS and Dept=T.Dept),iif(Dept=T.Dept and DateS=T.DateS, (SELECT count(*) from TB1 where Staff <= T.Staff and Dept=T.Dept and DateS=T.DateS))) as Rank,
Dept, DateS, Staff from TB1 as T ORDER BY Dept, DateS, Staff

Can anyone advice what I can do? I'm trying to do it in Access SQL. It would be easy to do it using SQL server, but I don't have them linked atm.

 

[John Big Booty]

Welcome to the forum.

Perhaps the attached sample will give you some pointers.

 

[avisccs]

Thank you for your reply.

This is one of those situations that I have been thinking about this 'problem' for a whole day, then I gave up and decided to post it on a forum for some help, got no reply for a few hours, then I rolled up my sleve and finally solved it on Friday night.

My solution is:
1. sort the table by manager, date and staff
2. add a column of autonumber, so that all staff have now got a unique number which is in correct order.
3. Use some SQL codes to rank the date, then rank the staff autonumber