Refer to previous record in current record query

[bejammin]

In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.

Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7

Is it possible to create some kind of simple expression to refer to data in another record?

Cheers,
Ben

 

[llkhoutx]

There's no simple solution other that via DAO or ADO code.

 

[shravshetty]

I found that solution to this problem would be DLookUp("[Field1]","Table1","[ID]=" & [ID]-1)

But In My case, my ID isnt in sequence.. Can any one help me out with the same??

 

[jcbur]

Hey shravshetty ,

I have a similar problem. My query is displaying the records based on my own criteria and sorted correctly. I cannot use ID when referring to previous or next records because they are not in sequencial order. I have been trying to determine how to add an autonumber or counter to my query so I can refer to the previous record via Dlookup.

Did you find any solution back in Aug 2012?

 

[jcbur]

The solution to reference previous record in a query when the ID's are not sequential is found on a Microsoft web site with the code PrevRecVal function. It worked exactly as desired!

Search for PrevRecVal on Google or put this in an open web browser to jump right to it: support.microsoft.com/kb/210504

Turn on the special DOA tool in the database as noted on the support site.

 

[gemma-the-husky]

i can see this is an old thread.

often the correct solution to something like this, is to think carefully about your data, and change your solution.

generally referring to other records in this way is not recommended, as in a database the concept of a "previous" or "next" record is nebulous. eg - what if you resort the data - what does that do to your "previous" record

it is often far more useful just to think in terms of the total value of a set of records.

 

[jcbur]

I am new to Access and this forum. I apologize. I will post a new thread next time.

I appreciate your response. My data is filtered and sorted in my query to provide a historical perspective for the user to view previous events and changes. I think I understand what you are trying to say. Please correct me if I am wrong.

Instead of relying on the previous and next terminology that can change based on the query sorting, I should have used the same criteria in my query to select the record I want to reference. I have some learning to do.

Thank You for your input