I have a need to develop an app to track the transfer of stock from one worker to another. At present I have my Materials Used table that has the fields:
[From] A ref field linked to my workers table
[To] a ref field also linked to my workers table
[Qty] unit of measure
[Product] ref field linked to my materials table.
From there I need to be able to look up and calclulate sum of net movements of each product by worker but I can’t figure out how to do it without splitting the table into parent/child, but that would make data entry more difficult, or using multiple Sum() Selects(). With the current structure, if I create a list of products and group it by [From] it will only pick up the stock that was transferred by [From] to [To], it won’t pick up the stock that went the other way.
e.g. if I had 3 widgets go from A to B and 4 go from C to A, I need to somehow be able to drill down on widgets for A and see that the net movement is +1 (3 out and 4 in).
Suggestions appreciated, or is Sum() Select() the only way it will work and remain tidy?