I'm creating database that stores personnel records.
One of its functions is to keep salary history. Based on the Salary History information, Annual & Actual salary for specific fiscal year is calculated.

There are couple problems that I'm looking to resolve:
1. Rate Increase:
If the start salary is given and so is % of rate increase, I could calculate the Annual End Salary.
However, what I need is for it to work the other way around. Given Salaries for each year, I want %
of rate increase to be calculated automatically.
2. What should I be doing if there's more than one raise during the fiscal year?

Thanks in advance for any suggestions.

Ella...

Edited by: ellachka on 08.04.04.

create a table linked on the personnel ID for salary data.

Salaries
IDfield, long integer
Salary, currency
StartDate, date
EndDate, date
IsCurrent, yes/no

I already have Salary Table with the following info:
EmployeeID, AnnualSalary, Review Date, Reason, FY (fiscal year), Notes

To find Rate Increase, I'm using this formula: AnnualSalary/PreviousAnnualSalary - 1

Any idea how to get and include PreviousAnnualSalary on the same line with the current salary?

Thanks

the table I suggested would JUST have the actual salary and fields to track when that value stopped and started. In addition to the fields I mentioned, you should add

LineNum, integer.

First salary is LineNum = 1, second is LineNum = 2, etc. This way, you could compare current vs previous

Review Date, Reason, FY (fiscal year), Notes .. should NOT be in this table. You should have just one record with this information per employee.

I'd go with something like what I have in the attached. If the review data is always tield to salary increase, you could keep it in the same table, IMO. If the employee can ever have a review that does not tie to salary increase then you would want a separate table for reviews only. If you wanted to check those folks who got no increase due to a bad review, I think it would be ok to have the review data there as well since the rateincrease would come out as 0%.

HTH
Noah

Thank you both so much. This problem has been bugging me for a while now. It looks like this is exactly what I need.