Solved Kind of like Excel ... (1 Viewer)

[Martyh]

Is there a way to add the previous row's cell "B" value to the current "B" cell and then

add the current row "A" value? Kind of like in Excel?

Thanks,
Marty

An Example:

A	B (Starting Point = 0)
35	35 = 0 + 35
15	50 = 35 + 15
4	54 = 50 + 4

 

[Pat Hartman]

This is trivial in a report (use the running sum property - no code needed) but much more complicated in a query (you need to use a non-equi-join to find the last Id less than the current one so you can add the two values. This method REQUIRES a UNIQUE, SEQUENTIAL identifier). Are you intending to store the calculated value? Poor practice because if you have to insert a row for some reason, you have to force a recalculation from that point forward.

We need some context to point you in the right direction

 

[MajP]

As previously stated to do this in a query you would need another unique sequential field like an autonumber.

SELECT table1.id,
       table1.fielda,
       (SELECT Sum([fielda])
        FROM   table1 AS B
        WHERE  B.id <= table1.id) AS RunningSum
FROM   table1;

 

[Martyh]

MajP Thank-you --- this works amazingly well!

Marty