Hi,
i have two tables, one is stock in and other is stock out table. stock balance from query, but stock balance shows minus figure and stock out accept the transaction.
Stock in/stock out/Stock Balance
120/100/20
0/40/-20
What I have tried:
SELECT tblRecvB.MCode, tblRecvB.GroupName, tblRecvB.MatName, tblRecvB.UOM, Sum(tblRecvB.Qty) AS Quantity, tblRecvB.Price
FROM tblRecvB
GROUP BY tblRecvB.MCode, tblRecvB.GroupName, tblRecvB.MatName, tblRecvB.UOM, tblRecvB.Price
UNION SELECT tblIssueB.MCode, tblIssueB.GroupName, tblIssueB.MaterialName, tblIssueB.UOM,Sum(-[Qty]) AS Quantity, tblIssueB.Price
FROM tblIssueB
GROUP BY tblIssueB.MCode, tblIssueB.GroupName, tblIssueB.MaterialName, tblIssueB.UOM, tblIssueB.Price;