A formula that refers to a value in the row above the current row?

[David Ball]

Hi,
In Access I have a query with a field, BERTHING F, that needs to have a date calculated based on the “ARRIVALCF” date and the “DepartDate”. The first date in the BERTHINGF column, 04-Jul-15, is hard coded in the table and does not change.
I need to have a date that is equal to the “ARRIVALCF” date, if that is later than the date in DepartDate for the row above, or use DepartDate + 1 day..
It is basically saying, a ship can berth on its arrival date, unless the previous ship is still berthed.
Also, the query is sorted by ARRIVALCF date ascending, so the order may change as arrival dates are updated in the main table. (I.e. I can’t just have a fixed ID number for each row and somehow use that in my formula).
This is OK in Excel because we can have a formula like =IF(M13>Q12,M13,Q12+1), but how can we refer to a value in a row above in Access?
Can this be done?
I have attached a diagram to try to make it a bit easier to understand.
Thanks
Dave

 

[spikepl]

You need to dump the excel concepts and learn DB terminology.

1. No such thing as row in Access - we deal with records
2. No such thing as "above" - records are held in tables and have no inherent order (like a bucketful of fish)
3. For records to be ordered you need to say what that order is, using the ORDER BY clause in a query
4. To extract data from another record use a subquery: http://allenbrowne.com/subquery-01.html

 

[CJ_London]

It can be done if your table is structured correctly. Note that next/previous etc in Access means nothing without an order - same with excel - your formula provides the 'right' answer because the rows are in a particular order, change the order and you get the wrong answer.

You would need to provide details of your table to get a fuller answer but in principle it would be something like this

SELECT ArrivalCF,Offloading,Departure, (SELECT max(DepartDate)+1 FROM myTable as T WHERE ArrivalCF<myTable.ArrivalCF) AS BerthingF, DepartDate
FROM myTable
ORDER BY ArrivalCF

 

[David Ball]

Thanks very much for the feedback.

Dave

 

[arnelgp]

SELECT ARRIVALCF, OFFLOADING, DEPARTURE, [BERTHING F], IIF(ARRIVALCF > DEPARTURE, ARRIVALCF, DateAdd("d", 1, DEPARTURE)) AS DEPARTUREDATE