Query for two table calculation

[tomtom1126]

Dear All,

I am new in this, and have a question for the below:

Table 1 for Product:
Product / Lot / Qty
ABC123 / A / 1000
ABC123 / B / 1100
ABC123 / C / 1200
ABC123 / D / 1050

Table 2 for Invoice:
Product / Lot / Qty
INV123 / A / 500
INV124 / A / 450


INV125 / A+B / 100

I want accessing helpling to assign the Lot in Table "2" ("A" / "B" / "C" .......).
exsample : Table "2" with 3 invoices total qty 1050, in table "1" deduct by lot "A" (1000), 50 will be deduct from lot "B" ........

MS Access can help for this?

Thanks so much for help.

Regards
Tom

[Minty]

You are looking a a basic inventory system.
Generally speaking it is very unwise to store running quantities, you should really have an transaction table that keeps the all the records of your stock movements In and Out as well as any stock checks so you can always then calculate your current balance.

Two guides are available to assist you with this - read and digest Allen Browne's Inventory page: http://allenbrowne.com/AppInventory.html

Also install the newest Northwind Sample database from the Access templates, I believe it has a sample inventory section as well.