need help please, tbls Relationship

[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

 

[John Vinson]

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.

Inventory systems are always a bit of a dilemma. UnitsInStock should,
in the purest theoretical sense, not be stored in any table AT ALL -
instead, it should be calculated on the fly by summing all the
transactions (100 units purchased originally, 15 sent out, 5 returned,
20 more sent out, etc. - 100 - 15 + 5 - 20 = 70 in stock).

A Relationship between the tables will not address this problem.

If you want to store the UnitsInStock field (and you may, depending on
the performance of the totals query you would otherwise need) you will
need to write VBA code in the BeforeUpdate event of the Form used to
enter transactions. This code must add or subtract the appropriate
amount before saving the record.

 

[MD]

Thanks John. as it currently is it does the calculation
and show result on the sub-form, but i wanted to have the
result to replace the UnitsinStock in the table which
seems to be not a good idea.

Thanks,

MD

-----Original Message-----