Query to obtain rows that have similar values in different columns
Posted on 2014-04-22
I have a table like below
Date Name Qty PurchaseID SaleID
04/14/14 Item1 4 3546
04/14/14 Item1 2 123 35461
04/14/14 Item1 1 234 35462
04/14/14 Item1 1 456 35463
the above table has both purchase orders and sale orders. The SaleID is created by the purchaseID + a new number. So the first 4 digits of a SaleID will be the corresponding purchase ID. I need to make sure that Qty on the purchase ID records is same as the total of the Qty in all the corresponding SaleID.
So the above table totals up correct. But if the last row was missing then the sales would total to Qty = 3 (or) if the last row Qty was 2, the Sales would total to Qty = 5, both of which are not equal to 4.
I would like to find all such rows where Purchase Qty <> total of Sale Qty for a given order.
Please help me build a query. I am running this query in MS Access. Thanks!