calculating data for a field based on another field

[briandavis]

In an employee database two fields I have are, hiredate and seniority. Is it possible to have access automatically enter a value in the seniority field based on the hire date. For example the person with the oldest hire date will be given a seniority of 1. the second oldest hire date gets 2 and so on.. For another example: say the employee who's seniority is 237 retires. If I delete his record from the table it will automatically recalculate and thus the employee who was 238 moves up to 237 and in fact all below this move up in the standings.

Thanks

 

[Dreamboat]

Maybe this will help you, Brian?
http://support.microsoft.com/?kbid=208946

I just did a google for:

calculate rank access database



I don't know the answers, but I can often find them.

 

[Dreamboat]

Oh, and we don't really do calculations in tables, so I moved this thread to queries.

 

[Mile-O]

Have a query sorting your table by StartDate (ascending)
Call it qrySeniority

Then, put this in a module:

Public Sub ReviseSeniority()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim lngCounter As Long


    Set db = CurrentDb
    Set rs = db.OpenRecordset("qrySeniority")

    With rs
        .MoveFirst
        Do While Not .EOF
            lngCounter = lngCounter + 1
            .Edit
            .Fields("Seniority") = lngCounter
            .Update
            .MoveNext
         Loop
         .Close
     End With
     db.Close

    Set rs = Nothing  
    Set db = Nothing

Exit Sub

Call that sub whenever you want to update the table.

 

[briandavis]

issue solved

Sorry, I went away on vacation since I posted this question.

Wanted you to know it works great!

I hope yo don't get tired of hearing that you're a genius!

 

[Dreamboat]

Oh, not at all!

I love to hear it!!

. . .


oh...I see.
you're talking about Mile...
hmph.


ROFL!