Seniority list with incremental number

[yoshi88]

Hi I have a database of employes and I need to sort them with a seniority number. This number will vary if employes quit or new one are hired. My table looks like this:

Dates (the name of the table)
No_employe (the primary key)
No_union (there is 2 different unions)
Date_seniority
Rank (if 2 employes have the same seniority date, they will have number 1, 2, 3, ... if not it's 0)

I need to order by No_union, Date_seniority and Rank after that I would like to have a number starting at 1 for the employe with the more seniority and going +1 for the second ...

Then I will use this number in some report and also in a few queries.

I tried a few things mention in earlier treads but I'm not really good with VB coding. Can someone help me with that problem?

Francois

 

[yoshi88]

I forgot to mention that I'm using Access 2000

Francois

 

[willir]

Hmmm

I can think of two ways...
- If you have a seniroirty number on the table or TEMP table, sort by Date_seniority / group by Union, move to an array, calculate the seniority number on the fly as you load the array and update the seniority number to the table. Obviously, you would have to create a maintenance procedure to update this.
- Create a recordset sorted by Date_seniority / group by Union, calculate the seniority number on the fly, and pass the employee number and rank to the report.

Richard

 

[yoshi88]

Thanks Richard for those 2 ways, I prefer your second solution but how do I calculate the seniority number on the fly?

Francois

 

[willir]

Francois

... If No_union is a text string (which I suspect it is not)

Public Sub RankIt()

Dim dbs As DAO.database, rstU As DAO.Recordset, rstE As DAO.Recordset
Dim intRank As Integer, strSQL As String, strQ As String

strQ = Chr$(34)

Set dbs = CurrentDb()

strSQL = "SELECT UnionCode from tblUNION ORDER BY No_union"
Set rstU = dbs.OpenRecordset(strSQL)

rstU.MoveFirst

Do While Not rstU.EOF

    strSQL = "SELECT * from tblEmployee WHERE No_union = " & strQ & rstU!UnionCode _
    & strQ & " ORDER BY Date_seniority"
    
    Set rstE = dbs.OpenRecordset(strSQL)
    
    intRank = 0
    
    With rstE
        rstE.MoveFirst
        
        Do While Not .EOF
            intRank = intRank + 1
        
            .Edit
            !rank = intRank
            .Update
        
            .MoveNext
            
        Loop
    
    End With
    
    rstE.Close

    rstU.MoveNext
    
Loop

rstU.Close

Set rstU = Nothing
Set rstE = Nothing
Set dbs = Nothing

End Sub

If No_union is numeric, then replace the second strSQL line with...
[tt]
strSQL = "SELECT * from tblEmployee WHERE No_union = " & rstU!UnionCode & " ORDER BY Date_seniority"
[/tt]

This is basicaly a "level break", but I created two record sets - one for the Union table, and one for the Employee table - to get away from the comparison...

strUnionCode = ""  'if string
'intUnionCode = 0  'if numeric

Do While not rst.EOF

    If rst.No_union <> strUnionCode Then intRank = 0

    intRank = intRank + 1

    'similar code

Loop

Hope this helps...
Richard