How would you resequence a collection of numbers

[Cameron]

I have a database that hold works and the dates they started working for the
organization. The dates produce a seniority number and as a new employee
starts that number is updated automatically.

But when an employee quits then they become inactive and when that status
changes then the seniority is re-calculated on the list. However, I have been
having some trouble getting the resequence to work.

I basically do a query of all the active employees and organize them based
on the start dates. i then have step through the query and change the
seniority date. But everytime this is performed it misses values like it
didn't run properly.

Public Function SenRecalc()
Dim MyDB As Database
Dim Myrecords As Recordset
Dim records As Integer
Dim X As Integer
Dim mysql As String

' used to recalculate the seniority list when an hourly employee goes
inactive
'
' Set Search string
mysql = "SELECT SeniorityDate, SeniorityLevel FROM Employees WHERE
([Employees.SeniorityDate] <> Null)"
mysql = mysql & "AND (Employees.Active = true) AND (Employees.EmpType =
'H') AND (Employees.SeniorityDate <> ' ')"
mysql = mysql & "AND (employees.SeniorityList = true) ORDER BY
SeniorityDate, Name ASC;"
Set MyDB = CurrentDb
Set Myrecords = MyDB.OpenRecordset(mysql)


records = Myrecords.RecordCount

Myrecords.MoveFirst
For X = 1 To records
Myrecords.Edit
Myrecords!SeniorityLevel = X
Myrecords.Update
Myrecords.MoveNext
Next

Myrecords.Close

Could you help out and find out what I might be doing wrong?

 

[Rick B]

Well, you don't store the seniority. It is a moving target.

Instead, simply create it in a query or report sorted by start date. The
oldest employee will be at the top. Then, create a sequential number.

There are several items here that explain how to create a sequential number
(line number).

http://groups.google.com/groups/sea...ublic.access+line+number+in+query&qt_s=Search

 

[Cameron]

Logically that would be easy to make. However, they want to be able to edit
teh seniority number if a couple of employees start on the same day. Some
employees return after they leave so seniority has to be updated to reflect
the loss in seniority. And they want to sort by crew in reports so if your
doing a sequential query of the data by date the numbering will not be right.

Suggestions?

Well, you don't store the seniority. It is a moving target.

Instead, simply create it in a query or report sorted by start date. The
oldest employee will be at the top. Then, create a sequential number.

There are several items here that explain how to create a sequential number
(line number).

http://groups.google.com/groups/sea...ublic.access+line+number+in+query&qt_s=Search


--
Rick B

I have a database that hold works and the dates they started working for
the
organization. The dates produce a seniority number and as a new employee
starts that number is updated automatically.

But when an employee quits then they become inactive and when that status
changes then the seniority is re-calculated on the list. However, I have
been
having some trouble getting the resequence to work.

I basically do a query of all the active employees and organize them based
on the start dates. i then have step through the query and change the
seniority date. But everytime this is performed it misses values like it
didn't run properly.

Public Function SenRecalc()
Dim MyDB As Database
Dim Myrecords As Recordset
Dim records As Integer
Dim X As Integer
Dim mysql As String

' used to recalculate the seniority list when an hourly employee goes
inactive
'
' Set Search string
mysql = "SELECT SeniorityDate, SeniorityLevel FROM Employees WHERE
([Employees.SeniorityDate] <> Null)"
mysql = mysql & "AND (Employees.Active = true) AND (Employees.EmpType =
'H') AND (Employees.SeniorityDate <> ' ')"
mysql = mysql & "AND (employees.SeniorityList = true) ORDER BY
SeniorityDate, Name ASC;"
Set MyDB = CurrentDb
Set Myrecords = MyDB.OpenRecordset(mysql)


records = Myrecords.RecordCount

Myrecords.MoveFirst
For X = 1 To records
Myrecords.Edit
Myrecords!SeniorityLevel = X
Myrecords.Update
Myrecords.MoveNext
Next

Myrecords.Close

Could you help out and find out what I might be doing wrong?