Difference in Sum(column) between two tables
-
Hi Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
WHERE tStk.[difference] <> 0
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTotI only want rows where the sum of the item qty is Different in the two tables The line:
WHERE tStk.[difference] <> 0
does not work. If I run the query from MS Access, it is asking for a value for the parameter tStk.[difference] I also tried
WHERE sum(tStk.Qty) <> tHST.Qty
but aggregate functions are not allowed in a WHERE clause.
-
Hi Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
WHERE tStk.[difference] <> 0
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTotI only want rows where the sum of the item qty is Different in the two tables The line:
WHERE tStk.[difference] <> 0
does not work. If I run the query from MS Access, it is asking for a value for the parameter tStk.[difference] I also tried
WHERE sum(tStk.Qty) <> tHST.Qty
but aggregate functions are not allowed in a WHERE clause.
Try taking your existing query and make it a sub-query. Some thing like: Select * from ("put your query here") XX where XX.tStk.Qty <> tHST.Qty Remember to remove your "Where" clause from your inner query.
-
Hi Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
WHERE tStk.[difference] <> 0
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTotI only want rows where the sum of the item qty is Different in the two tables The line:
WHERE tStk.[difference] <> 0
does not work. If I run the query from MS Access, it is asking for a value for the parameter tStk.[difference] I also tried
WHERE sum(tStk.Qty) <> tHST.Qty
but aggregate functions are not allowed in a WHERE clause.
Maybe HAVING sum(tStk.Qty) <> tHST.Qty[^] would work better
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Maybe HAVING sum(tStk.Qty) <> tHST.Qty[^] would work better
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Awesome! Thanks Jörgen
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
HAVING sum(tStk.Qty) <>tHst.HstTotI see that: Aggregate functions can be used as expressions only in the following: The select list of a SELECT statement (either a subquery or an outer query). A COMPUTE or COMPUTE BY clause. A HAVING clause.
-
Try taking your existing query and make it a sub-query. Some thing like: Select * from ("put your query here") XX where XX.tStk.Qty <> tHST.Qty Remember to remove your "Where" clause from your inner query.
Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'