Re-number records in a table based on dates

[RevJeff]

Hi...I have a Job table that has the following fields.

EmpID
JobNo
JobClass
BgnDate​

We track job history for up 5 jobs for each employee, so the field [JobNo] will have a value from 1 to 5. Your first job will be "1", your second job will be "2" and so on up to 5. If an employee starts a 6th job, then [JobNo] "1" gets over written with the new [JobClass] and [BgnDate], but keeps the same [JobNo] value "1". If you get a 7th job, then [JobNo] "2" gets over written, but keeps the same [JobNo] value "2" and this pattern continues for every new job you get.

What I want to be able to do is to update the [JobNo] value, so that the employee's current job, the job with the latest date in the [BgnDate] field is assigned job number 1, then the next latest job is assigned job number 2 and so on.

The number of jobs an employee can vary, some may only have one job, where others may have 2, 3, 4 or 5.

I am attaching a spreadsheet that has the current data and and example of what I am looking for. (Note that the job number changes based on the [BgnDate])

Thanks for any help you can offer.

Jeff

 

[ajetrumpet]

JEFF,

this is better done in excel with IIF() statements in cells, because access will require you to (most likely) use code to manipulate your recordsets. access queries do not work vertically, from record to record, as so many people have asked before.

 

[RevJeff]

Thanks, but I was hoping to avoid that. This is going to be done on a regular basis and was looking for some kind of code that could take care of this.