Solved Calculate from previous record in a datasheet, or continuous form (1 Viewer)

[Cotswold]

I would like to calculate the difference between a field in one record to the same one in the previous record in a datasheet.

basically on the the 1st record I want to show the number of days back to the second and so on

1st record 12/03/26 =2
2nd record 10/03/26 =1
3rd record 09/03/26 =97
4th record 02/12/25 =....
...................

is this possible?

 

[isladogs]

See the approach used by Allen Bowne for meter readings using a subquery

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[arnelgp]

SELECT YourTable.DateField, 
[DateField]-(Select Top 1 T.DateField From YourTable As T Where T.DateField < YourTable.DateField Order By T.DateField Desc) AS Diff
FROM YourTable
ORDER BY YourTable.DateField DESC;

 

[Ken Sheridan]

To return a non-updatable recordset:

SELECT
    T1.*,
    NZ (
        T1.TransactionDate - (
            SELECT
                MAX(TransactionDate)
            FROM
                Transactions AS T2
            WHERE
                T2.TransactionDate < T1.TransactionDate
        ),
        0
    ) AS DaysElapsed
FROM
    Transactions AS T1
ORDER BY
    T1.TransactionDate DESC;

Less efficiently, to return an updatable recordset:

SELECT
    Transactions.*,
    NZ (
        Transactions.TransactionDate - DMAX (
            "TransactionDate",
            "Transactions",
            "TransactionDate < #" & FORMAT(Transactions.TransactionDate, "yyyy-mm-dd") & "#"
        ),
        0
    ) AS DaysElapsed
FROM
    Transactions
ORDER BY
    TransactionDate DESC;

The above both assume that the TransactionDate column contains distinct values.