access update query with calculations

[ozdemirozgur]

Hi,

I am new with queries and need some hlpe. I have tow tables. Companies and Salaries. The salaries table has the following fields.

Salaries.CompanyId,
Salaries.EmployeeId,
Salaries.Position (can be manager, supervisor and officer)
Salaries.Year,
Salaries.Salary,
Salaries.Bonus.

Basically, it shows the salary and bonus of an employee for a specific year. CompanyId, EmployeeId and Year are together primary index of the table.

Companies table looks like below;

Companies.CompanyId,

Companies.SalaryManager_2010,
Companies.BonusManager_2010,
Companies.TotalEarning_2010,

Companies.SalaryManager_2009,
Companies.BonusManager_2009,
Companies.TotalEarning_2009,

Companies.SalaryManager_2008,
Companies.BonusManager_2008,
Companies.TotalEarning_2008,

Companies.SalaryManager_2007,
Companies.BonusManager_2007,
Companies.TotalEarning_2007,


I need a query which should update the salary, bonus and total earning for each position on the companies table. It should first select a company on the companies table, then go to the salaries table and for each year group the records based on the positions, get the totals and update the company table back.

thanks for your help,
ozgur