I’d much rather see the use of references between Orders table and the Item table
- this would remove the need for a hard-coded
SELECT()and would instead run on the efficient
REF_ROWS()built by the system.
Then your formula would look like this…
[Units] - SUM([Related Orders][Units])
…and would be much more efficient. If you plan on accumulating a decent amount of records, that
SELECT() will come back and bite you - FYI.
Yeah, you right… Works the same for me, but this is much easier and efficient, Thank you.
But… that doesn’t take into consideration WHICH item it should be pulling the [Unit] value for.
That will pull the complete list of every value in the table… and sum them all.
So if you’re looking for a grand total of every [Unit] value added together, that’s the way to go.
- But if you need things filtered specific to the [ItemID] - you’ll have to include the
References my friend
- On the Order table, create a reference to the Items table - you likely already have a field were people can select the item (or items).
Very good tips @Denis_Sanchez_Leyva