How to update 2 tables by filling up one form only?

[hungpuiki]

1. I am developing a inventory management system.

2. My system comprises of the following tables (attributes):-
   (i) Product (ProductID, Product_description)
   (ii) Location (Location)
   (iii) Transaction (DateTime, ProductID, Qty_change, Location)
   (iv) Available locations (ProductID, Location, Available_qty)

3. The system has a Transaction_form, which allows users to fill in how many pieces of inventory he wishes to move in or move out, and to which location he will be moving. After filling up this form, the system will create a row in the “Transaction” table. However, i want the system to update also the “Available locations” table too!

4. Therefore, if the user inputs that 10pcs of product “A” is taken away from location “1L1A”. The system should search in the table “Available location” if there is a row containing “1L1A” in location and product “A” in product ID. If there is, the “Available qty” of that row should minus 10pcs. Alternatively, the system should create a new row in the table “Available location” recording that available qty of product “A” in location “1L1A” is “-10”.

5. Creating the table “Available location” is essential because I want to create a virtual column (related available locations) in “product” table. Such that when i click the product_detail page, the user can see the available location where he can find that particular product.

I would be so grateful if anyone could advise me how to solve this problem. Many thxxx