Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Vancouver, Br. Columbia, Canada
    Thanked 0 Times in 0 Posts

    Access previous record of query (97)

    Is there an efficient way to retrieve fields from the previous record of a query using just SQL?

    I have a series of records with (among others) a field that is the timestamp when the record was created, and I need to calculate the elapsed time between readings by subtracting the two timestamps. If there was a sequential record number in the query, I could do a self-join using the next-lower record number, but that's not feasible with this recordset. The only idea I've had is to populate a temporary table with a sequential field plus the original record ID, but I would like to avoid creating another table if possible. TIA
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Access previous record of query (97)

    Microsoft has a solution using a user-defined VBA function in ACC: Referring to a Field in the Previous or Next Record; this solution is also available in the Samply Queries database (the article contains a link to download it). Note: the first solutions described require an ID field without gaps; the solution with a VBA function is lower down.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts