Update a field, but move data to existing field

[ehouseholder]

Hi,
I am new to Microsoft Access and am creating a database to hold salary information. I have several fields, ex. Current salary, yr1 salary, yr2 salary, etc...

When I update the salary information for an employee (i.e. an employee gets a raise), I would like the current data to move to yr1 salary, yr1 salary to move to yr2 salary, yr2 salary to move to yr3 salary, etc... That way I am able to track the salary information over the past years. Does this make sense? Does anyone know of an easy way to make this happen? Thanks in Advance!

 

[GoDawgs]

I think you can just set up an update query for this...then in the "Update to" field for Current Salary just put the current salary, for yr1 Salary put [Current Salary], for yr2 Salary put [yr1 Salary] and so on depending on how you want to update...it should keep the values until you've updated everything. I'm not positive on this, but give it a try in a test table. Hope that helps.

Kevin

 

[ehouseholder]

Thanks for the suggestion. I tried it, but it didn't work out. Instead of doing a query, do you know how to make it happen on the form, and update the table as new data is recorded? I've looked a little bit into using the before and after update events, but am not familiar with them enough to know how they actually work. Any ideas are greatly appreciated. Thanks Again!

 

[GoDawgs]

Hmmm...you could always work your way backwards. Say you keep 3 years of data (Current Salary and then yr1 and yr2). On the form have a text box bound to each of the fields, then have a fourth text box that is unbound but labeled Current Salary. Make it equal to the bound Current Salary text box to start with, but allow changes...and then on the "After Update" event of the unbound text box put some code like this in:

me.txtyr2.value=me.txtyr1.value
me.txtyr1.value=me.currentsalary.value
me.currentsalary.value=me.txtcurrentsalarytheycanchange

Hope that helps...and makes sense.

Kevin

 

[ehouseholder]

I will give it a shot. That sounds like it should work. Thanks!