Such a strange issue I find myself with this morning when working with tables
So I have two tables, connected and merged into one 'merged table' using a query to produce one full table of all the data.
In the merged table, I have appended columns to calculate fields within the same table.
If I add a field/column to either of the tables, the connected table elongates as expected, however the named formulas within the merged table change to reflect what is now sitting in the previous column position.
Sample Data
[Anticipated_Cashflow]
[ProjectData]
The connected query producing the merged table then has all the above data in one table.
I've then appended column(s) to the right to calculate:
[Merged_Table]
At this point all is working well, exactly as exptected.
Now, If I insert a column into [ProjectData] table, the merged table extends to incorporate the new column of data, however the pre-existing table formulas are not preseverd and retain the cell locations where the data was before.
[Merged_Table]
Obvisouly the results are completely different, but its not noticeable at first glance as the formulas return results. Its only under inspection that the formulas are incorrect.
I don't understand why the formulas are changing and not retaining their formulas.
Its like I want to 'absolute' the table formula, but this isn't a possibility.
Perhaps I'm just going about this all wrong lol.
Let me know your thoughts please.
Moxy
So I have two tables, connected and merged into one 'merged table' using a query to produce one full table of all the data.
In the merged table, I have appended columns to calculate fields within the same table.
If I add a field/column to either of the tables, the connected table elongates as expected, however the named formulas within the merged table change to reflect what is now sitting in the previous column position.
Sample Data
[Anticipated_Cashflow]
Project Name | 2020 | 2021 | 2022 |
|---|---|---|---|
Project 1 | £50000 | £100000 | £50000 |
Project 2 | £0 | £50000 | £100000 |
Project 3 | £200000 | £200000 | £200000 |
[ProjectData]
Project Name | Apples | Bananas | Pears |
|---|---|---|---|
| Project 1 | 10% | 50% | 40% |
| Project 2 | 100% | 0% | 0% |
| Project 3 | 20% | 30% | 50% |
The connected query producing the merged table then has all the above data in one table.
I've then appended column(s) to the right to calculate:
[Merged_Table]
| Project Name | Multiple columns (Cashflow to/and Product Data Entries) | 2020-Apples | 2020-Bananas | 2020-Pears |
|---|---|---|---|---|
| Project 1 | ~ | =[@2020]*[@Apples] = £5000 | =[@2020]*[@Bananas] = £25000 | =[@2020]*[@Pears] = £20000 |
| Project 2 | ~ | =[@2020]*[@Apples] = £0 | =[@2020]*[@Bananas] = £0 | =[@2020]*[@Pears] = £0 |
| Project 3 | ~ | =[@2020]*[@Apples] = £40000 | =[@2020]*[@Bananas] = £60000 | =[@2020]*[@Pears] = £100000 |
At this point all is working well, exactly as exptected.
Now, If I insert a column into [ProjectData] table, the merged table extends to incorporate the new column of data, however the pre-existing table formulas are not preseverd and retain the cell locations where the data was before.
[Merged_Table]
| Project Name | Status | Multiple columns (Cashflow to/and Product Data Entries) | 2020-Apples | 2020-Bananas | 2020-Pears |
|---|---|---|---|---|---|
| Project 1 | Tendering | ~ | =[@2021]*[@Bananas] | =[@2021]*[@Pears] = | |
| Project 2 | Tendering | ~ | =[@2020]*[@Bananas] | =[@2021]*[@Pears] = | |
| Project 3 | Tendering | ~ | =[@2020]*[@Bananas] | =[@2021]*[@Pears] = |
Obvisouly the results are completely different, but its not noticeable at first glance as the formulas return results. Its only under inspection that the formulas are incorrect.
I don't understand why the formulas are changing and not retaining their formulas.
Its like I want to 'absolute' the table formula, but this isn't a possibility.
Perhaps I'm just going about this all wrong lol.
Let me know your thoughts please.
Moxy
