Adding 2 fields together from 2 different tables 1

[Woodyuk]

This should be very easy to do, but I cant do it. I have 3 tables. One has a list of Employees, one has a score in it for the perfomance for a month, and another has there performance for a year.

I have pulled out a unique code, from the employee table and the 2 other tables have this code in them in which they are linked. Now when I pull out the employee code and this shows every result in that table, which is correct. If i add the performance from the month table it puts the result next to the correct code, or leaves it blank. Now what im trying to do is add the performance from the month table and year table. How ever currently the year table is blank, so Im expecting to get a result of just the month table, but I get nothing. I only get the employee code with a blank field next too it.

So all I want is these 2 fields too add together. Also if there is no record it should have a 0 next to the code.
can any one help me?

Here is the SQL code by the way, just incase anyone wondered about my links

SELECT tblEmployee.Code, [tblMonth].[Performance]+[tblYear].[Performance] AS Overall

FROM (tblEmployee LEFT JOIN tblMonth ON tblEmployee.Code = tblMonth.Code) LEFT JOIN tblYear ON tblEmployee.Code = tblYear.Code

GROUP BY tblEmployee.Code, [tblMonth].[Performance]+[tblYear].[Performance];

 

[Golom]

Try this

SELECT 
  tblEmployee.Code, 
  (NZ([tblMonth].[Performance])+NZ([tblYear].[Performance])) AS Overall

FROM (tblEmployee LEFT JOIN tblMonth ON tblEmployee.Code = tblMonth.Code) LEFT JOIN tblYear ON tblEmployee.Code = tblYear.Code

You don't need the GROUP BY because you are not using aggregate functions.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Woodyuk]

Superb! that does the job spot on. Dont know why I had that grouping in there. Been playing with this DB just getting grips too it. What does NZ stand for then?

 

[Golom]

NZ is a builtin function in Access that supplies a default value if the first argument is NULL.

You can Code it as NZ(Arg1, Arg2) which returns "Arg2" if Arg1 IS NULL, or "Arg1" if it IS NOT NULL.

If you leave out Arg2 then it returns
- 0 if Arg1 is a numeric type
- '' (empty string) if Arg1 is Text.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]