I need some help with creating a query. This is an inventory application where Product1 is moved from location A to location B and then to location C. Location B should then have zero inventory for Product1. I have a table that stores all the movements as follows:
ProductName | QtyMoved | LocationFrom | LocationTo
"Product1" | 500 | "LocationA" | "LocationB"
"Product1" | 500 | "LocationB" | "LocationC"
ProductName, QtyMoved, LocationFrom, LocationTo
LocationFrom = 'LocationB'
LocationTo = 'locationB';
(total qty of Product1 in locationFrom for locationB) minus (total qty of Product1 in locationTo for locationB).
I would make a UNION query to add the moves into a location and subtract the moves out of a location - sommething like
Select ProductName, LocationTo as Location, QtyMoved as Balance from Movements Union All Select ProductName, LocationFrom, -QtyMoved from Movements
After you save this query, you can use it as the source for a 2nd query to get the balances, something like
Select ProductName, Location, Sum(Balance) as Balance from [Movements Summary]