Updating a number field when one record is deleted
In access (2003) I am setting up a table with Employee # (Pri. Key), senority numbers..and
so on. However I cannot figure out how to reset the senority numbers when one leaves. For example Bob is #235 senority and leaves (By retiring, quits or gets fired)..well Mary was #236 and now should be #235 (because Bob is no longer at the company)..and so on with everyone else below Bob getting their Senority number changed by one.>
Do you have any ideas..I tried autonumber, but it will not do that.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 5, 2004 4:49 PM
UPDATED:
October 6, 2004 9:17 AM
Answer Wiki:
If I have understood your question, once you have deleted the employee from the database, you need to increment all employees numbers below that person by 1. If this is the case, a simple update query should achieve this, such as: UPDATE Contacts SET Contacts.FaxNumber = contacts.faxnumber +1 where faxnumber > 10;
Steve
To see all answers submitted to the Answer Wiki: View Answer History.
I made it a point to never delete any records out for future reporting, best solution would be to add another field that would indicate whether the employee is active or nonactive. Then utilize a query when running the reports that will exclude the inactive record and sort by seniority with the autonum on the report to renumber the employees.
Most of the time when dealing with employee records, you should never delete the record unless you are moving it to a history table or something like that. Adding a column for active is a good idea. If the seniority number needs to be in increments of 1, I might suggest yet another column for the “OriginalSeniority” just in case you need that information. When making the employee inactive, update the OriginalSeniority with the Seniority number and then set the seniority number to 0 and then an update statement like this “Update Employee set seniority = seniority -1 where seniority > 235″. If the seniority number doesn’t need to be in increments of 1, I would just leave it be. You can report on seniority for active employees sorting by seniority number without it being in increments of 1