Display value from previous record in current record

I have a table of meter readings that I use to calculate usages.  I would like to create a query/database view that would allow me to display the previous day's reading in the same row with the current reading.
Aaron GreeneProgrammerAsked:
Who is Participating?
Bill PrewCommented:
Here's the basic idea if it is just current rows date minus one day.  We just join it to itself and connect to get prior day relative to current row.

SELECT t1.ReadingDate, t1.ReadingText, t2.ReadingDate, t2.ReadingText
FROM tblReadings AS t1 INNER JOIN tblReadings 
AS t2 ON t1.ReadingDate-1 = t2.ReadingDate;

Open in new window

Bill PrewCommented:
Will the prior days reading always be exactly one day before the current reading?  Or could there be a larger gap, so we need to look for the latest one before current, as opposed to one day before current?

Aaron GreeneProgrammerAuthor Commented:
The rows will always be spaced at a known interval. Most of the time they will be 24 hour intervals, but there are some instances where they will be spaced 1 hour apart. They will always be consistent.
Bill PrewCommented:
So do you need more help?

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Here's a function that you may find easier/faster to use if your doing this in a form.

 By passing a form reference (Me or [Form]) and the name of a field, it will return the value from the previous row as currently displayed in the form.


Function GetPreviousRow(frm As Form, strFieldName As String) As Variant

        ' Fetches value of field from previous row
        Dim strBM As String
        Dim rstClone As Recordset

        ' Get the bookmark for the current row.
10      strBM = frm.Bookmark

        ' Now create the record set clone, and make it
        ' refer to the same row as rst, which is on the same
        ' row as the form.
20      Set rstClone = frm.RecordsetClone
30      rstClone.Bookmark = strBM

        ' Move the clone record set to the previous row.
        ' If this puts us at the BOF, then the result has to be
        ' FALSE, and leave the function.
40      rstClone.MovePrevious
50      If rstClone.BOF Then
60        GetPreviousRow = Null
70      Else
          ' If you're not at BOF, then retrieve the necessary info.
80        GetPreviousRow = rstClone(strFieldName)
90      End If

100     rstClone.Close
110     Set rstClone = Nothing

End Function

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.