Calculation Field help please
I have two tables the first one is an inventory table with a promary key field labeled Product ID and then a field called current inventory. My second table is a transaction table that has fields for Product ID, Transaction Codes (with a drop down value list) and a number of other fields.
I am trying to place a calculation in the "current inventory" field that either adds 1 or subtracts 1 based on the 3-letter transaction code in the transaction table. I have tried the Case calculation but it only seems to work on one occurance of a particular transaction code and not for multiple occurances.
Any advice would be much appreciated. Thank You!