some complex joining access sql query

[elsc]

here my database tables

CASH
Id ProductId Qty
INVOICE
Id ProductId Qty
PURCHASE RETURN
Id ProductId Qty

STOCKIN
Id ProductId Qty
CREDITNOTE
Id ProductId Qty


so my stock balance should be (STOCKIN.Qty + CREDITNOTE.Qty) - (CASH.Qty + INVOICE.Qty + PURCHASE RETURN.Qty)

how to do this in access sql?

[RhinoBull]

You may try executing sql statement that looks similar to the following:

SELECT
c.ProductId,
SUM(s.Qty + cn.Qty) - SUM(c.Qty + i.Qty + pr.Qty) 'Stock_Balance'
FROM CASH c, INVOICE i, PURCHASE_RETURN pr,
STOCKIN s, CREDITNOTE cn
WHERE i.ProductId = c.ProductId
AND pr.ProductId = c.ProductId
AND s.ProductId = c.ProductId
AND cn.ProductId = c.ProductId