summing columns from two tables

[scotty22]

Hi there,

I'm trying to sum up the values from two columns in two different tables using this SQL statement:

SELECT cl.Scheme, (Sum(cl.Gross1)+ Sum(cd.Gross2)) AS SumOfGrossRes
FROM cl INNER JOIN cd ON cl.Scheme = cd.Scheme
GROUP BY cl.Scheme
HAVING (((cl.Scheme)="ABC" Or (cl.Scheme)="DEF" Or (cl.Scheme)="GHI"));

What it seems to be doing is summng the values, for a particular scheme, from both tables, then repeating this for the amount of times it appears in the second table.

So if a scheme appears three times in the cd table then it adds the values from the first table to the second table, then repeats this another two times.

Is there a way to prevent this?

Kind regards,

Scotty22

[alansidman]

Try changing your join from inner to left

Alan

[scotty22]

Doesn't change anything.

I'm more curious to know as to why it's doing this. I can't understand why it would be designed to repeat the summation as it does.

Kind regards,

Scotty22

[rpeare]

try changing your HAVING to WHERE

If that doesn't work post an example database or at least show us the structure of your two tables with example data.