Les Programmer Les Programmer - 1 year ago 75
SQL Question

access 2016 query

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:

Movements (

Two entries might be:

ProductName | QtyMoved | LocationFrom | LocationTo
"Product1" | 500 | "LocationA" | "LocationB"
"Product1" | 500 | "LocationB" | "LocationC"

This query lists the above entries:

ProductName, QtyMoved, LocationFrom, LocationTo
LocationFrom = 'LocationB'
LocationTo = 'locationB';

What I want is to calculate and list the total Qty in 'locationB' for 'Product1', (which would be zero, in this case).

I would like the datasheet view of the query to show 3 columns,
. I believe what I need is an expression like:

(total qty of Product1 in locationFrom for locationB) minus (total qty of Product1 in locationTo for locationB).

Perhaps this would be easier if I was to create a report instead of a query. Any suggestions would be appreciated.

Answer Source

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]