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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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;


Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Santoscardenas
    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.
    0 pointsBadges:
  • Randym
    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
    1,740 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: