Here is an explanation of my database and what I am trying to accomplish. The Question is at the bottom. Any help you can find would be appreciated. Thank you for looking into it!
The tables and their fields I have in the database are as follows…
EmployeeT
EmployeeID – Primary Key – Number (not an autonumber because they are county assigned)
LastName – Text
FirstName – Text
EmailAddress – Text
JobTitle – Text
MobilePhoneNumber – Text
Notes – Memo
ItemT
Item – Primary Key – Text
Description – Text
Category – Text
CostPerItem – Text
Supplier – Text
SupplierItemNumber – Text
ESBuildingQty – Number
D3Qty – Number
Stock Total – Calculated – [StockAmountESBuilding] + [StockAmountD3]
Comments – Memo
StockLocationT
StockLocation – Primary Key – Text
UsageLocationT
UsageLocation – Primary Key – Text
TransactionTypeT
TransactionType – Primary Key – Text
AddRemoveESBuilding – Text
AddRemoveD3 – Text
TransactionT
TransactionID – Primary Key – Autonumber
Item – Text
TransactionType – Text
Quantity – Number
StockLocation – Text
UsageLocation – Text
DateTime – Date/Time
EmployeeID – Number
Notes – Memo
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either “ES Building” or “D3” when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
How would I accomplish this?
Thank you.
The tables and their fields I have in the database are as follows…
EmployeeT
EmployeeID – Primary Key – Number (not an autonumber because they are county assigned)
LastName – Text
FirstName – Text
EmailAddress – Text
JobTitle – Text
MobilePhoneNumber – Text
Notes – Memo
ItemT
Item – Primary Key – Text
Description – Text
Category – Text
CostPerItem – Text
Supplier – Text
SupplierItemNumber – Text
ESBuildingQty – Number
D3Qty – Number
Stock Total – Calculated – [StockAmountESBuilding] + [StockAmountD3]
Comments – Memo
StockLocationT
StockLocation – Primary Key – Text
UsageLocationT
UsageLocation – Primary Key – Text
TransactionTypeT
TransactionType – Primary Key – Text
AddRemoveESBuilding – Text
AddRemoveD3 – Text
TransactionT
TransactionID – Primary Key – Autonumber
Item – Text
TransactionType – Text
Quantity – Number
StockLocation – Text
UsageLocation – Text
DateTime – Date/Time
EmployeeID – Number
Notes – Memo
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either “ES Building” or “D3” when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
How would I accomplish this?
Thank you.
How to calculate fields with restrictions
