I want to retain records backward and change them forward. Our employee pay rates are increasing in July, but records and information we query based on their old pay rates need to be maintained. In our Access database, if I enter the new pay rates into the table, all former records are affected. What can I do to retain all old records based on old pay rates, and change all new records to be based on the new pay rates? thank you.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
retaining records
- Thread starter filo55
- Start date
tlbroadbent
MIS
The correct answer depends on your database and table structures. I'll venture a guess and suggest a couple of methods to do what you want.[ol][li]Copy the entire table to another table for archive purposes. You'll then be free to update the original table.
[li]If the above method is not acceptable for some reason, then add a revision column to the table, if such a column doesn't already exist. If you add column, initialize it to 1 or whatever you want the base revision to be.
Insert new records for every employee with a new pay rates, updating both the pay rate and the revision as you insert the records.[/ol]Hope this helps. Terry
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes
[li]If the above method is not acceptable for some reason, then add a revision column to the table, if such a column doesn't already exist. If you add column, initialize it to 1 or whatever you want the base revision to be.
Insert new records for every employee with a new pay rates, updating both the pay rate and the revision as you insert the records.[/ol]Hope this helps. Terry
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes
MichaelRed
Programmer
I would tke the PayRate out of the Emp table and create a new [related] table for the PayRate. It would have fields:
[EmpId] [StDt] [PayRate] [EndDt]
[EmpId] is, of course the Key, relating back to the Employee Table.
StDt is the Starting (effective) date for the payrate
[PayRate] is, of course the Rate of pay effective on the [StDt]
[EndDt] is the date when the payrate (of this record) is changed/altered ...
So, when you use this, the Current rate is always just the one with no [endDt]. For all other purposes, you need to use the date of interest and find the record where the date is between [stDt] and [EndDt]
The scheme can - obviously - do without the [EndDt], simply by using the Most recent record for the [EmpId] as the current payrate, but then finding the correct rate for other dates becomes (slightly) more complex. Keeping the [EndDt] field simplifies thr retrieval of pay for "back dated" inqurires at the expense of needing to be SURE that whenever there is an adjustment to the pay, it is filled in.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
[EmpId] [StDt] [PayRate] [EndDt]
[EmpId] is, of course the Key, relating back to the Employee Table.
StDt is the Starting (effective) date for the payrate
[PayRate] is, of course the Rate of pay effective on the [StDt]
[EndDt] is the date when the payrate (of this record) is changed/altered ...
So, when you use this, the Current rate is always just the one with no [endDt]. For all other purposes, you need to use the date of interest and find the record where the date is between [stDt] and [EndDt]
The scheme can - obviously - do without the [EndDt], simply by using the Most recent record for the [EmpId] as the current payrate, but then finding the correct rate for other dates becomes (slightly) more complex. Keeping the [EndDt] field simplifies thr retrieval of pay for "back dated" inqurires at the expense of needing to be SURE that whenever there is an adjustment to the pay, it is filled in.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
MichaelRed
Programmer
I would tke the PayRate out of the Emp table and create a new [related] table for the PayRate. It would have fields:
[EmpId] [StDt] [PayRate] [EndDt]
[EmpId] is, of course the Key, relating back to the Employee Table.
StDt is the Starting (effective) date for the payrate
[PayRate] is, of course the Rate of pay effective on the [StDt]
[EndDt] is the date when the payrate (of this record) is changed/altered ...
So, when you use this, the Current rate is always just the one with no [endDt]. For all other purposes, you need to use the date of interest and find the record where the date is between [stDt] and [EndDt]
The scheme can - obviously - do without the [EndDt], simply by using the Most recent record for the [EmpId] as the current payrate, but then finding the correct rate for other dates becomes (slightly) more complex. Keeping the [EndDt] field simplifies thr retrieval of pay for "back dated" inqurires at the expense of needing to be SURE that whenever there is an adjustment to the pay, it is filled in.
The situation you are facing is a direct result of not-normalizingn the database tables at design time. While this is a common situation, it is also one of the primary causes of most db failure. You shoud either get someone (a PROFESSIONAL) to review your db or at the very least, do a VERY THOROUGH internal review. The review (wheather from an independent professional or your internal group needs to include managers of all departments and functional areas of the company
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
[EmpId] [StDt] [PayRate] [EndDt]
[EmpId] is, of course the Key, relating back to the Employee Table.
StDt is the Starting (effective) date for the payrate
[PayRate] is, of course the Rate of pay effective on the [StDt]
[EndDt] is the date when the payrate (of this record) is changed/altered ...
So, when you use this, the Current rate is always just the one with no [endDt]. For all other purposes, you need to use the date of interest and find the record where the date is between [stDt] and [EndDt]
The scheme can - obviously - do without the [EndDt], simply by using the Most recent record for the [EmpId] as the current payrate, but then finding the correct rate for other dates becomes (slightly) more complex. Keeping the [EndDt] field simplifies thr retrieval of pay for "back dated" inqurires at the expense of needing to be SURE that whenever there is an adjustment to the pay, it is filled in.
The situation you are facing is a direct result of not-normalizingn the database tables at design time. While this is a common situation, it is also one of the primary causes of most db failure. You shoud either get someone (a PROFESSIONAL) to review your db or at the very least, do a VERY THOROUGH internal review. The review (wheather from an independent professional or your internal group needs to include managers of all departments and functional areas of the company
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
tlbroadbent
MIS
MichaelRed is right again. His solution is normalized whereas mine would not be. Terry
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question