Performing Calculations from Linked Table


I have table one with columns ID (Primary Field, Auto Number), Date, Product, Opening Value, Closing Value.

Table two has ID (Primary Field, Auto Number), Linked ID (Linked to Table one, shows ID of Table one), Date (Lookup Field), Product (Lookup Field).

Then in Table two, I want to add a column Total Value which should be a calculation of (Closing Value - Opening Value) from Table one.

Can this be done?


Yes and No…
It can’t be done with a single field. What you would need to do is create three fields:

Lookup(Closing Value)=Close
Lookup(Opening Value)=Open
Formula(Close - Open)

Hopefully that makes sense


This works - you could also add just one field to Table 1 that calculates {Closing Value} - {Opening Value}, then rollup that value in a single field on Table 2.