Updating a Field with A Sum From 2 Other Tables

[Guest]

I'm new to this whole game, so hopefully I'm asking this right. I have 3
tables I'm working with. One where I have some summary employee information,
one where I have information about their initial hiring information, and one
where I keep track of any raises they have received over the years. I want
to create a query that updates the summary table with a "Current Salary" -
where Current Salary = Starting Salary from Table 3 + Sum of All Raises from
Table 2. I have the primary keys setup, with an employee ID linking all
three tables, but I can't seem to write a query to do this. I get errors
about aggregate functions in update queries, I tried creating a 4th table and
adding the values in that and that didn't work. Any ideas?

 

[Guest]

Hi Red,

Can you please list the table names and also the corresponding field names?
this will help to find a solution.

Eskimo

 

[Marshall Barton]

I'm new to this whole game, so hopefully I'm asking this right. I have 3
tables I'm working with. One where I have some summary employee information,
one where I have information about their initial hiring information, and one
where I keep track of any raises they have received over the years. I want
to create a query that updates the summary table with a "Current Salary" -
where Current Salary = Starting Salary from Table 3 + Sum of All Raises from
Table 2. I have the primary keys setup, with an employee ID linking all
three tables, but I can't seem to write a query to do this. I get errors
about aggregate functions in update queries, I tried creating a 4th table and
adding the values in that and that didn't work.

Your summary table sounds like it's in violation of the
rules of Relational Database Normaliztion. That value is
easy enough to calculate on the fly whenever you want to
display ot, but saveing the calulation in a table will be
out of date when you forget to rerun your update query every
time anyone gets a raise.

 

[Guest]

Got it. Guess I'm just so used to excel where you want to calculate interim
values to make it easier to debug etc.

Thx,
-Cam

 

[Marshall Barton]

Right!
Databases are far more than suped up spreadsheets.

A vague general guideline in designing your
tables/application is to ask yourself:

Question:
How many things are affected when a user
enters/edits any value?

Answer:
Only one field in one row in one table with no
changes to table stucture, queries, forms or reports.