Salary Adjustments (1 Viewer)

[nathanmav]

Hi everyone.. just want to ask on how to make a history like for example i have a table of salary of all the employee.. i just want to know how to update the salary of each employee but store the old salary for tracking purposes.. do i need to make a separate table for the old record? thanks in advance!

 

[plog]

To keep a salary history you need to create a seperate table not just for the old record, but for all the salary records. This table will look something like this:

Salaries
EmployeeID, Salary, SalaryStart, SalaryEnd
1, 150000, 1/1/2011, 1/1/2012
2, 150000, 1/1/2012,
1, 151500, 1/1/2012,

This table will allow you to determine each employees current salary as well as history.

 

[nathanmav]

thanks po the reply... ok i will make another table for that how about the relationship salary table to salary history. and do i need to make a query? sori im stuck to this situation i dont know what to do..

 

[plog]

You might need to make a query. What data would you like?

 

[nathanmav]

i need to make an update of the current salary and store the old salary to the table so that i can check when was the last increment and effective date of each employee and display the updated salary to the main employee salary table.. thanks

 

[plog]

I can't really understand what you want. Can you provide sample data of whats in your table and then what data you want returned?

 

[nathanmav]

attach is the sample database what im doing the problem is in the update salary. for example i want to update the current salary because the employee got his increment but the problem is when i edit the salary it will update easily but the old salary will gone.. and i want a history for that to monitor.. and also the database is made in ms access 2010 version.. thanks again..

 

[plog]

Again, you need to seperate employees and their salaries. You should have a table of employ data:

EmpCode, Gender, MStatus, JobTitle, etc...

Then you should have a salary table similar in function to what I described earlier. Then when you need to update salary, you enter a data into their salary record with a blank SalaryEnd field and start a new record for that employee.

 

[nathanmav]

i attach the sample database i follow what you said i have two table the tblemployee and tblsalaries.. i still dont understand on how to make a salary history sorry because im newbie in ms access.. can u make me an example based on the sample i attached.. thanks again..

 

[plog]

You're 90% of the way there. You need to populate your SalaryStart date fields in tblSalaries. If these are the first salaries of each employee, you should populate that field with each employee's Hire Date. If an employee no longer works there, populate the SalaryEnd field with their EndDate value.

Now suppose Jacob gets a raise in Salary of 500 starting 1/1/2009. You would first find his current record (the one with a blank 'EndDate' field) in tblSalaries and put '12/31/2008' into the EndDate field. You would then create a new record in that table using his EmpCode, 1/1/2009 for SalaryStart and then putting all the correct values for the other fields into that record. This would then have a blank EndDate which signifies it is his current salary.

 

[nathanmav]

hi again.. thank you very much i already got the logic on how to make a salary history..

i just want to ask regarding the salary end is it possible to make this automatic when i add new record and put the salary start the value will goto to the salary end of the previous salary.. so that I will not enter it first. do i need to make a query? thanks again.. i attached the sample i made..

 

[plog]

Yes. You can add code to your form that automatically finds the open record and inserts today's date. I would ask this question in the Forms section of this forum.

 

[nathanmav]

thanks.. how can i ask this in forms forum? can you teach me how to code this?