Suggestions on how to structure my table(s)

[Griff]

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?

[Steve · Accepted solution]

I imagine you’d have a slice on the Materials Used table that produces a filtered list according to user-provided criteria. The user-provided criteria would be captured in a separate table with one row per user. The criteria would be collected through a detail view with quick edit columns. The same detail view would include the desired calculated-summary values, and possibly even the list of the matching rows.

[Griff]

Thanks Steve. That’s ‘kinda’ what I was thinking, though I wanted to work off a more dynamic list rather than user entering criteria, Nevertheless your answer inspired me to think of a related approach. I think I will try to create a dashboard where one form lists the workers, and when the user selects a worker, other forms in the dashboard show lists of materials with totals plus a list of transactions in separate in and out forms. I was hoping there may be a more commonly used, less resource hungry approach but I think I can make this work. Thanks again.