Table to query multiplication.

[Greyowlsl]

Hi Guys,

I didn't know where to post this, but i figured some VBA would be needed somewhere.

Okay so;

I have a table 'PARTS', below is a small example:

            [B]PARTS[/B]
   PART         MODEL A      MODEL B 
     X             5           6
     Y             6           7
     Z             8           3
     H             3           2
     J             2           1

Note, there are around 600 different parts, and up to 100 different models. Also the data is shown in a 'parts' form, where a part is selected and then a text box for each model displays the amount that model uses of the part.
_________________________________________________________
Next i have a query called 'SALES', below is a small example

     [B]SALES[/B]
   MODEL         SOLD
     A            3          
     B            12           
     C            9           
     D            20           
     E            230

This is updated live when a new product is sold.
______________________________________________________
Next I have a table 'STOCK 6/6/12', below is a small example:

  [B]STOCK 6/6/12[/B]
   PART          STOCK    
     X             50                 
     Y             45                 
     Z             32                 
     H             60                 
     J             50

___________________________________________________

I need to show on a table or query the available stock.

This is the idea i have:
I need to multiply the sales times the amount of parts that are used in each product, this will tell me how many parts to subtract off the stock.

The tables and queries cannot be changed unfortunately.

Thanks for your time

,Leon