With such structure, what you mean with 2. row? are you moving the product from store to BAG2, or out from BAG2?
No need for 2 quantity fields. movement between 2 locations (stores and BAG2) must always have 2 records (or a single record with one quantity field, and 2 location fields - one incoming, another outgoing). The sign of quantity is entered with quantity, or is determined by MovementType.
Like (MovementType values are 1 for purchase, -1 for scraping, 2 for internal incoming and -2 for internal outgoing)
Code:
BatchID MovementDate, MovementType, KitID, ProductID, Qty
1233 04/10/2018 1 Stores CSKU1980 20
1233 04/10/2018 -2 Stores CSKU1980 3
1233 04/10/2018 2 BAG2 CSKU1980 3
1233 04/10/2018 1 Stores CSKU2020 20
Now the query to calculate saldo of all products in stores will be
Code:
SELECT ProductID, SUM(MovementType*Qty) As StockQty FROM tblProductLog WHERE KitID = "Stores" Group by ProductID
Calculating the total number of all products together doesn't give you anything useful! Who cares that total number of cars and pears you have is 10 Having 10 pears is not too much
The query to calculate the current number of every product in every kit will be
Code:
SELECT KitID, ProductID, SUM(MovementType*Qty) As StockQty FROM tblProductLog WHERE KitID <> "Stores" Group by KitID, ProductID