Select Statement please help

[hussein2000]

hello Friend
i am using msaccess as database and vb6 front end.
and i have two tables in my database one is purchase which has two fields
and another has to fields which has alos two field.
so here i want to retrive the balance from purchsae and sales

purchase table

ProductId Quantity
10 100
11 50
10 15
10 35
11 40
11 65



Sales table
ProductId Quantity
11 7
10 2
11 5
10 4
SO HERE I WANT THE BALANCE OF PURCHASE AFTER SALES


total quantit purchased from each item is

product id quantity purchas quantity sales

10 150 6
11 155 12



I TRY THIS BUT I DID NOT GET FULL RESULT
BECAUSE SUBQUERY RETURN ONLY ON VALUE( RECORD)

so the balance FOR PRODUCT ID 10 IS 144 AND FOR 11 IS 143
SELECT Q.ProductId
, Sum(Q.Quantity) AS QltPurchased, (SUM(Q.Quantity)) - (SELECT SUM(S.Quantity) from Sales as S GROUP BY S.ProductId ) AS Balance
FROM Quantity AS Q
WHERE Q.ProductId in(select Q.ProductId from Quantity as Q)
GROUP BY Q.ProductId;

THANKS

[Sabin_33]

Try This
P - Purchase Table
S - Sales Table


SELECT Sum(PurQty) - Sum(SalQty) as Balance FROM
(
SELECT P.Quanity as PurQty, 0 as SalQty, Id
FROM P
WHERE ID = 10
UNION ALL
SELECT 0 as PurQty, S.Quanity as SalQty, Id
FROM S
WHERE Id = 10
)