automatically renumbering field based on new data

  • Thread starter Thread starter smith
  • Start date Start date
S

smith

I have a Human Resource database which contains a
seniority field. This field can change frequently when
staff resigns. I want to be able to automatically
renumber the remaining staff seniority's when a staff
member is deleted from the active table.

Can this be done easily?
 
I have a Human Resource database which contains a
seniority field. This field can change frequently when
staff resigns. I want to be able to automatically
renumber the remaining staff seniority's when a staff
member is deleted from the active table.

Can this be done easily?
Click to expand...

I would recommend calculating this on the fly, rather than storing it.
Assuming that seniority is determined by the sequence of Hiredate,
oldest being highest, you could use a Subquery:

SELECT EmployeeID, LastName, FirstName, ... ,
(SELECT Count(*) FROM Employees AS HireSeq
WHERE HireSeq.HireDate <= Employees.HireDate) AS Seniority
FROM Employees;
 
Thank you for your help! Could this be done by using two
fields in the calculation. Seniority is based partly on
hire date, but also on employee id #. The reason being
often we have numerous hires on the same date.

When a new staff is brought on, we know their seniority,
it is when we want to re-calculate the seniority upon
termination of an employee that this gets sticky.

I appreciate your assistance!!!
 
Thank you for your help! Could this be done by using two
fields in the calculation. Seniority is based partly on
hire date, but also on employee id #. The reason being
often we have numerous hires on the same date.

When a new staff is brought on, we know their seniority,
it is when we want to re-calculate the seniority upon
termination of an employee that this gets sticky.
Click to expand...

Yes, you can use as many fields as needed to sort the records. Let's
say

SELECT EmployeeID, LastName, FirstName, ... ,
(SELECT Count(*) FROM Employees AS HireSeq
WHERE HireSeq.HireDate <= Employees.HireDate
AND HireSeq.EmployeeID <= Employees.EmployeeID) AS Seniority
FROM Employees;
 
Excuse me jumping in, but I hope you are not using Auonumbers for
EmployeeID, and expecting to sort on them ? Dont ever replicate your
database !

Much better to extend the Date field to a true Date/Time field and sort on
that, if you want to base seniority purely on order of hiring.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Excuse me jumping in, but I hope you are not using Auonumbers for
EmployeeID, and expecting to sort on them ? Dont ever replicate your
database !
Click to expand...

Excellent point, Adrian! Yes, Autonumbers all become Random on
replication; EmployeeID 331238715 might be followed by -1884234084.
 
Thank you both for your assistance. We are not using
Autonumber on the employee id number. I will try your
suggestion. Again thank you for your help!!!!
 
Back
Top