multiplied results - strange

[Luciana Travassos]

In a design consult, i have three fields, the first one is result of a group,
and the others are sums from fields of two tables.
Here is the SQL:
SELECT COM_SERV_COM_TOTAIS_1991.Quadra,
Sum(COM_SERV_COM_TOTAIS_1991.A_Construida) AS SomaDeA_Construida,
Sum(COM_SERV_COM_TOTAIS_2004.A_Construida) AS SomaDeA_Construida1
FROM COM_SERV_COM_TOTAIS_2004 INNER JOIN COM_SERV_COM_TOTAIS_1991 ON
COM_SERV_COM_TOTAIS_2004.Quadra = COM_SERV_COM_TOTAIS_1991.Quadra
GROUP BY COM_SERV_COM_TOTAIS_1991.Quadra;

When I get the results, they are all multiplied by 2...

Any ideas about this problem?

Thanks,
Luciana

 

[PatHartman]

Sum each table independently and then join the results. You are ending up
with a Cartesian Product because one or both tables have multiple rows for
the value of Quadra. The problem is being caused because Jet does the join
(which is creating the "duplicates") first and then sums the results. To
get the correct results, you need to sum first and join last and the only
way to do that is with nested queries (my preferred method) or sub-selects
(correct but I find them more confusing and more difficult to test).