How to fill table field with an arithmetical operation of fields from other two tables? RRS feed

  • Question

  • I have a database with several tables, this is my first time designing a database, my problem is I don't know how to fill a table field, with information from two other tables...

    One of my tables is called product, in this table I have a field called stock, I would like this field to "autofill", for this table the PK is called Prod Code, I have no much knowledge about database design but I believe these would be the steps I need in order to get the calculated value I want to insert in this field:

    First find the PK in another table called entrance (using select command i guess...)

    then get the value of the field qty

    then find the PK in other table called exit(again select command) *for these case the PK may not exist in the table

    after that get the value of the field qty

    Finally calculate a subtraction from the first qty minus the second and store this calculated value in the Product table in the field stock

    As you see I have a general idea but no clue of how to implement it, could you please help me? Thanks a lot in advance

    • Edited by r4ul90 Saturday, November 13, 2010 9:44 PM typos
    Saturday, November 13, 2010 9:42 PM