Retrieve data from previous/specific row in query

[mbthaz]

How does one access the value in a previous row. ie. get the percentage difference between the values in the same column but in consecutive rows.

 

[kraxmo]

Use a ADO (or DAO) recordset to process through the entire table. You will need to add a new difference column.

Pseudo code:

read first row
store column value
do while more rows
diff = 1 - ([current column] / [stored column])
[difference column] = diff
update
[stored column] = [current column]
read next row
end do

Access 2000:

Function UpdateIt(reftbl As String, refcol As String, pctcol As String)
    Dim rs As ADODB.Recordset
    Dim intValue As Integer
    Dim dblPct As Double

    Set rs = New ADODB.Recordset
    rs.Source = "SELECT " & refcol & ", " & pctcol & " FROM " & reftbl
    rs.ActiveConnection = CurrentProject.Connection
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
    rs.Open
    intValue = rs(refcol).Value
    rs.MoveNext
    Do While Not rs.EOF
        dblPct = 1 - (intValue / rs(refcol).Value)
        rs(pctcol).Value = dblPct
        rs.Update
        intValue = rs(refcol).Value
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Function

Jim Kraxberger
Developing Access solutions since 1995

 

[cmmrfrds]

If you have an unique sequential number in the table that establishes the order, then you can try a self join. For example id number.


select A.id, A.value1, B.value1
from tab as A, tab as B
where A.id < B.id