Total not correct if query 2 tables

[Guest]

Hi,

SELECT mdb_phy.Loc, Sum(mdb_phy.Qty) AS [Phy Qty], [mdb-sys].Loc,
Sum([mdb-sys].Qty) AS [Sys Qty]
FROM mdb_phy INNER JOIN [mdb-sys] ON mdb_phy.Barcode1 = [mdb-sys].Barcode1
GROUP BY mdb_phy.Loc, [mdb-sys].Loc;

I tried to run above query but the result of is wrong. If I run above query
the result is Phy Qty = 6443 and Sys Qty = 6576.

If i run the query just with one table:-
SELECT mdb_phy.Loc, Sum(mdb_phy.Qty) AS [Phy Qty]
FROM mdb_phy
GROUP BY mdb_phy.Loc;

I get Phy Qty = 6505

If i run the query just with one table:-
SELECT [mdb-sys].Loc, Sum([mdb-sys].Qty) AS [Sys Qty]
FROM [mdb-sys]
GROUP BY [mdb-sys].Loc;

I get Sys Qty = 4182

Why if I run the first query the result is wrong? where is my mistake? Pls
advise.

FYI, barcode1 is the prmary key for both table.

Thank you in advance.

Mark Magesen
In peace we live, together we achieve =)

 

[Jeff Boyce]

Mark

From your description, your "two-query" solution appears to only return
records for summing that match in both tables. Your "one-table" solutions
appear to return ALL records in each table, for summing.