Insert Query based on previous records. 1

[Vyurr]

Hello all,

Every year, employees receive a salary increase based on our collective agreement. I have a database that tracks salary costs and I need to update to the new salary values. The salary table is structured as follows:

tblSalary

EmployeeID (FK to tblEmployees)
SalaryID (Autonumber PK)
Salary
StartDate
EndDate

The EndDate for all employees is currently set to Oct 31, 2008 as the new salary takes effect November 1st. The increase to each employees salary is 2.5%.

I would like to avoid generating a new record for every Employee with a start date of November 1st. I suspect I can save myself a lot of work with an Insert Query, but I'm not sure how I would go about inserting a new record for each Employee with a new Salary, startDate and endDate.

Is this possible or am I stuck keying?

(I have a very basic knowledge of VBA, so if it cannot be done with a query, is a Function the answer?)

Thanks in advance,

V

 

[dhookom]

If salaries have always increased then you could use SQL like:

INSERT INTO tblSalary (EmployeeID, Salary, StartDate, EndDate)
SELECT EmployeeID, Max(Salary) * 1.025, DateAdd("yyyy",1,Max(StartDate)),  DateAdd("yyyy",1,Max(EndDate))
FROM tblSalary
GROUP BY EmployeeID;

I would test this on a copy of the salary table prior to a final run.

If salaries might have gone up and/or down, you will need to use a subquery to find the most salary of the most recent salary record for each employee.

Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

Thanks for your prompt response.

Employees do change positions which can affect their salary positively and negatively. Would something like this work?

INSERT INTO tblSalary (EmployeeID, Salary, StartDate, EndDate)
SELECT EmployeeID,(Salary) * 1.025, DateAdd("yyyy",1,Max(StartDate)), DateAdd("yyyy",1,Max(EndDate))
WHERE Max(EndDate)
FROM tblSalary
GROUP BY EmployeeID;

Thanks again,

V

 

[dhookom]

That would not work. I would probably create a new query (qselCurrentSalary) with each employee's current salary information.

SELECT s1.SalaryID, s1.EmployeeID, s1.Salary, s1.StartDate, s1.EndDate
FROM tblSalary s1
WHERE s1.StartDate = 
 (SELECT Max(StartDate) FROM tblSalary s2
  WHERE s1.EmployeeID = s2.EmployeeID)

You can then use this query as the source for your append query.

INSERT INTO tblSalary (EmployeeID, Salary, StartDate, EndDate)
SELECT EmployeeID, Salary * 1.025, DateAdd("yyyy",1,StartDate), DateAdd("yyyy",1,EndDate)
FROM qselCurrentSalary

[red]Again, make sure you test this prior to running on your actual table.[/red]

Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

You sir are a very wise man. Thanks for the help, have a star