MS-Access queries

[sabeel]

i have created two tables in ms-access as goodsin and goodsout... i need to find out the stock of materials by calculating the sum of goodsin minus sum of goodsout according to material code and finally display the output as the number of materials are left from this date to this date... pls help me to write this code.....

[nico5038]

i have created two tables in ms-access as goodsin and goodsout... i need to find out the stock of materials by calculating the sum of goodsin minus sum of goodsout according to material code and finally display the output as the number of materials are left from this date to this date... pls help me to write this code.....

Best to use multiple queries. First make a GroupBy query to total the goodsin by MaterialCode and do the same for the goodsout, but (when the qty is positive) add * -1 to the field to turn it into a negative value.
Now create a UNION like:

select MatCode, Qty from qryGoodsIn
UNION ALL
select MatCode, Qty from qryGoodsOut
 

Finally you can use another GroupBy query to total the goods per MatCode.

Getting the idea ?

Nic;o)