Operation using the previous row data

[PaulGiscard]

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

[artemus]

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.

[PaulGiscard]

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...

[artemus]

1. Create a new query that is a copy of your current one.
2. Keep only rows that refer to the closing balance.
3. In your main table remove all closing balance rows.
4. Subtract 1 from the Week no column.
5. Merge your closing balance table with your main table using the Week no column.

[PaulGiscard]

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 🙂