Total not correct if query 2 table.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 =)
 
Do a quick query to see if every Barcode1 exists in both tables. Sounds like
for some records it only exists in one or the other, hence why rows (and
hence smaller SUMS) are being returned
 
Back
Top