How to subtract sum() from 2 queries and return result in 1 row?

[hugebreakthrough]

I need your help ....

I have a table which name is tblQty with this fields:

|Source|Target|Product|Quantity|

I want to get the total stocks. Which currently I subtract the SUM result of quantity from target to source as STOCK IN with the SUM result of quantity from source to target as STOCK OUT so if the records like this:

|Source |Target|Product|Quantity|
|VendorA|StoreA| Prod1 |200 |
|VendorB|StoreA| Prod1 |100 |
|VendorC|StoreA| Prod2 | 50 |
|StoreA |StoreC| Prod1 |120 |
|StoreA |StoreD| Prod2 | 25 |


So for the table like this I run 2 query to get the total available stock of product 1 in Store A:

SELECT SUM(Quantity) FROM tblQty WHERE Product='Prod1' AND Target='StoreA'

Which the result is 300 Then I subtract this in VB with second query:

SELECT SUM(Quantity) FROM tblQty WHERE Product='Prod1' AND Source='StoreA'

Which the result of available stock is 180, it comes from 300 - 120

(1) How to write one sentence query for this that return the stock in value,stock out value and total stocks available in one row?

Like this:

product | Store | Stockin | StockOut | Stocks Available |
Prod1 | StoreA | 300 | 120 | 180 |

(2) How to get the list of each products in each stores per row in one single query and return result like this:

product | Store | Stockin | StockOut | Stocks Available|
Prod1 | StoreA | 300 | 120 | 180 |
Prod1 | StoreC | 120 | 0 | 120 |
Prod2 | StoreA | 50 | 25 | 25 |
Prod2 | StoreD | 25 | 0 | 25 |

Thanks a lot

 

[cmmrfrds]

You can try this as a starting point. Self join the table on the target and source. Also, if any sum turns out to be null you will need to plug a zero.


Select
product,
target,
sum(tar.quantity) as stockin,
sum(src.quantity) as stockout,
(iif(sum(tar.quantity) is null,0,sum(tar.quantity))
- iif(sum(src.quantity) is null,0,sum(src.quantity)))
as available
from tblQty as tar
inner join tblQty as src
On tar.product = src.product
and tar.target = src.source
group by product, target