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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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?

Bill PrewIT / Software Engineering ConsultantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.