Access Updating inventory levels

Oct 12, 2017
Reaction score
Let me say at the onset, I am an amateur Access user. If my questions seem somewhat elementary, it is probably because of my lack of experience. Thanks for your understanding and patience....

I have a Master Parts table (Material_Master) that has a field called Current_Inventory_Level. I also have a table for Transactions (Transaction_Master) that has a field called Transaction_Total. When a number of pieces of stock are either sold (-) or returned (+) the current inventory level needs to reflect this change.

The way I have it set up now, the transaction is recorded in the transaction table and the total number of pieces either sold or returned is recorded in the table. I need to be able to use that transaction total (in the Transaction Master table) to adjust the current_inventory_level in the Material_Master table.

Apparently, I can't use a calculated field from a query in an update query. To use the current_inventory_level as a "base" number and just perform historical calculations based on all the transactions for that particular part seems absurd not to mention dangerous and poor programming in my estimation. So, how does one do this process of updating so that the current_inventory_level always reflects an accurate number after each transaction is executed?

Your suggestions are greatly appreciated! Thanks in advance!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question