"Array" type query structure

[DanWall]

Hi.

I'm having a problem, that I hope someone can help me sort.
I'm trying to avoid the use of VBA of possible, and just rely on the native macro functions of Access. (I'm using 2007)

If I have a query, or a form's datasource that looks like so....

tbl.ID     tbl.Name    tbl.Age
1          Dave         23
3          Bob          19
4          Steve        42
6          Rick         37

I want to add an expression that lists the PREVIOUS person's age.
IE: if it were vb code, it would look something like

name(i).age=name(i-1).age

Notice that the ID's are not concurrent, as it is used in a subform.

Thanks in advance for any advice you may have.

 

[khawar]

Download the attached sample and check the query query1

 

[DanWall]

Perfect.

Thanks very much khawar!