Forum Discussion
Operation using the previous row data
Hi all,
Appologies if this has been answered elsewhere but I can't find a solution that works for me in the other posts...
My problem is I need to add data and use result in the next row.
Here is what I'm trying to do:
| Week no | Opening Balance | Storage | Closing balance |
| 5 | value coming from a diferent query | opening balance week 5 + pallets in week 5 | Storage week 5 - Pallets out week 5 |
| 6 | Closing balance week 5 | Opening balance week 6 + pallets in week 6 | Storage week 6 - Pallets out week 6 |
The data I have is Opening balance for the first row as a manual input converted to a simple query
For the pallets in and out I have it coming from another table which looks like the below:
| Week no | Pallets in | Pallets out |
| 5 | 174 | 197 |
| 6 | 176 | 74 |
There might be a very easy way to this but I can't find the best option
Thanks,
Paul
4 Replies
- Create a new query that is a copy of your current one.
- Keep only rows that refer to the closing balance.
- In your main table remove all closing balance rows.
- Subtract 1 from the Week no column.
- Merge your closing balance table with your main table using the Week no column.
Thansk for the quick response artemus.
I had thought of that, but would that mean that I have to do it 4 times? I might take another look in the morning after a while away from the screen 🙂
I'm not sure why you think it would take 4 times... but I don't see your dataset.
Another option is to adjust the week no for closing balance rows to match the other rows, and then do a pivot to get a column for each event type in a week.
Hi Artemus, I must not understand properly your steps then...
Here is the data I'm working with:
A query with my week numbers
A query which has my total pallets in and total pallets out for the different weeks of my data set (some weeks might be 0 and therefore don't show up in that table)
A query which is drilled down to return the opening balance for the first week.
If Week number is n, n+1, etc... I need to create the following table:
Where Opening balance week n is OPWn, Storage is SWn, Closing balance is CBWn
Pallets in week n is PIWn, Pallets out Week n is POWn
Opening balance for the first week is x (manual input)
Week number Opening balance Storage Closing balance n x SWn= x + PIWn CBWn = SWn - POWn n+1 OPWn+1 = CBWn SWn+1 = OPWn+1 +PIWn+1 CBWn+1 = SWn+1 - POWn+1 n+2 OPWn+2 = CBWn+1 SWn+2 = OPWn+2 +PIWn+2 CBWn+2 = SWn+2 - POWn+2 I hope that makes more sense and sorry if the solution is the same, I just can't work out what your instructions mean for my data...
