Strange behavior with SUM() on related field
I'm having a strange problem performing a sum on related fields, and I'm not sure whether it's a bug or if I'm doing something wrong.
I work for a company that does specialized installations of A/V and computer gear. We keep a small inventory of miscellaneous connectors & cables, and I'm trying to build a database to track it. I have 3 tables. "Inventory" keeps track of the individual items, using an existing alphanumberic part number as the primary key. "Job Codes" keeps track of a list of codes from the accounting system that the items get charged to. "Stock Transactions" keeps track of individual transfers in and out of the inventory, and relates items to jobs.
Keeping track of individual stock transfers is important because at the end of the month this information has to be entered into the accounting system. I'm looking at ways to do an import, but for now it's a manual process. In order to keep track of how many items are in the inventory, I have started with an "Initial_Quantity" field that is set when the record is created. There is also a Quantity_In_Stock field that tracks the amount of each item on hand via a calculation, which I'll get to in a moment.
Each transaction record has a "Qty_Start" field that stores the Quantity_In_Stock at the time the record is created (via a lookup). It also has a Qty_Transferred that is entered by the user (either + or -). This results in a Qty_Remaining field that serves as a check for the user, but is otherwise not used.
The calculation for Quantity_In_Stock is as follows:
Initial_Quantity + Sum ( Stock Transactions::Qty_Transferred )
My intent is that for any selected Inventory record, the Quantity_In_Stock will be the start quantity plus the sum of all transfers from records that are related by that Item Number. And it works, to a point. If I select a Item Record in the Inventory table, the Quantity_In_Stock field calculates correctly.
Unfortunately, when I create a new Stock Transaction record and remove a certain number of items from stock, the Inventory record that was last selected before switching to the Stock Transfer view will have that same quantity removed as well - even if it's a different item number.
So for example, if I have Item ZAUDCAB001 open in a form view of the Inventory table, and I switch to a Stock Transfer view, and then subtract 50 ZMBOOT002 items, then go back to the Inventory view, both ZAUDCAB001 and ZMBOOT002 will show a Quantity_in_Stock that is 50 less than what I had before.
Can anyone tell me if this is a bug, or whether I've done something horribly wrong in my design?