Storing result of a calculation into a higher level table

Discussion created by FrankFarmer_1 on Dec 16, 2016
Latest reply on Dec 31, 2016 by BeatriceBeaubien

I have a database for investments, which contains (amongst other tables) a table describing each INVESTMENT (stocks, bonds, term deposits, mutual funds etc etc). Attached to this is another table of TRANSACTIONS (buys, sells, reinvested dividends, interest earned, stock splits etc etc). Each investment will have one or more transactions.


I have a script which, using the transactions, calculates the adjusted cost base (ACB) for each stock-type investment, and it produces a report showing the results for each stock/mutual fund etc. The results are correct (this script is discussed elsewhere in this discussion forum, where I had a problem exiting nested loops -- that's been resolved). This script also attempts to store the ACB result into the "mother" INVESTMENT table, so I can readily see the latest ACB for any investment. The last command, before the exit, stores the ACB result (at the Transaction level) into a field in the Investment table, for the investment corresponding to these transactions. I use a Set Field command to move the ACB result into the higher table.


However, this process doesn't always work as desired. After running the ACB script first, then looking at the content of the Investment table, about 90% of the  ACB's are stored correctly in the Investment table. For some strange reason, the others are either blank, or have erroneous results. It appears quite random, as I haven't figured out why certain investments are not getting their correct ACB figure (which appear in the ACB report). Repeating the process produces the same results, so the investments affected with the error don't change -- always the same investments. Strange, since they're all treated the same way in the ACB script, and that script does produce correct results for each investment, as shown in the resulting report. The mystery is why don't the correct ACB results always get placed in the higher (Investment) table?