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!
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!