Making History Easy

[Amy Klein]

Hi,

Our Human Resources department is setting up in Access 97
our employee database. What is the best way to make
history? Example: In our employee database, we have a
field called Current Salary. When an employee receives a
raise, how do we input that so that the Current Salary
field is updated, but the old salary still exists for
possible reference. We want to make this change in a Form
so it will automaticlly update the current salary and keep
the old salary for furture reference. We are not sure
how to do that?

Thank You,
Amy Klein
Workforce Safety & Insurance
(701) 328-6025

 

[Douglas J. Steele]

Since each employee can (hopefully) have many salaries, you're describing a
1-to-many relationship. You need 2 tables: one for the employee indicative
information, and one for the salary information. The salary information
table should have effective and expiry dates (where the current record would
have an expiry date of Null).

To use the current date, you'd join the two tables in a query, joining on
Employee ID where Expiry Date Is Null. Use that query where you would
otherwise have used the table.

 

[Tom]

Amy:

Just last week, I dealt with a similar process... I
needed to track different location of some merchandise.

Add the few lines of code into an AfterUpdate event (it
kicks in when the value of the salary field changes).


&&&&&&&&&&&&&&&&
strSQL = "Insert into tblSalaryHistory (YourAutoID,
Salary) " & vbCrLf & _
"VALUES ('" & Format(Me!YourAutoID.Value, "00000")
& "', '" & Me!Salary.Value & "')"

MsgBox strSQL, , "Salary history table will be
updated!"

CurrentDb().Execute strSQL, dbFailOnError
&&&&&&&&&&&&&&&&


A few additional pointers:
- Ensure you have a primary key (YourAutoID)
- Make sure that the salary history table permits that
the YourAutoID field can except duplicates. After all, a
record (employee) might have multiple salary changes

HTH,
Tom