Les Programmer Les Programmer - 3 months ago 16
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 (
ProductName,
QtyMoved,
LocationFrom,
LocationTo
)


Two entries might be:

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


This query lists the above entries:

SELECT
ProductName, QtyMoved, LocationFrom, LocationTo
FROM
Movements
WHERE
LocationFrom = 'LocationB'
OR
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,
ProductName
,
QtyMoved
and
'LocationB'
. 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

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]