Need help in defining tbls relationships please

[MD]

hi,

I am developing this application for our shop to track
materials in/out the shop. i have a tbl and form for
materials in (tblProducts, frmProducts)and a tbl and a
form for materials out (tblTransaction and
frmTransaction), each product has a unique product ID
which will be same for both tbls and a job number feild,
the jonnumber for materials in is always the shop job
number 8645 and never changes but in tblTransaction it
will be different from transaction to Transaction. I also
need to deduct what goes out from what comes in the shop
to refelect the current status of specific item. I have
been able to do this dedeuction using a different table,
a query and a subform on the frmTransaction but it
doesn't change the value of the UnitsInStock after the
Transaction was made.

Please help put some light into my path by helping me on
ways to define the relationship between theses two three
tables and ways to have the value of the UnitsInstock
replaced after a transaction is made.

Your help is greatly appreciated.

Regards,

MD