AutoUpdate Query/Table ??

[wtrimble]

My business has a certain number of "modules" in inventory. One table has module number, # of modules initially and other info. In a form, the warehouse employee needs to enter in the Module number and how many modules are outgoing to a plant which will be saved in another table. Say we have 12 modules initially and a week later 3 are sent out. On another entry another employee will enter in that 4 modules were sent out to another plant, leaving 5 remaining in inventory.

I need to be able to look up how many modules are remaining in inventory by searching by module number. The number of rows is unknown for each module, (they might be sent 3 at a time, or one at a time).

How can I do this?? I thought about using Dlookup but am not sure how to look up and add the amount of "outgoing" entries for multiple rows of entries and subtract them from the "initial" inventory #.

Any suggestions?

 

[apr pillai]

1. Create a Total Query using the trasactions table Grouped on Module Number and Sum up the number of Modules sent out Value.

2. Open a New Query and insert the Main Inventory Table and the Query created in step-1.

3. Create a Link on Module Number between both Queries.

4. Insert the Module Number, Inventory Total Modules Columns from the main Table and the transaction sum up value in the new Query Columns.

5. Subtract the transactions total value from the Total Modules Value of the Main Table to get the Balance in Stock.

6. Save this new Query with a name. You can open this Query any time to view the latest position of stock in Module Number-wise.

 

[wtrimble]

Great idea, thanks a lot Aprpillai.