How to use data from different row in query formula

[bilojax]

I want to write a formula in a query that pulls data from the record above. I have all my records numbered sequentially. I want the formula to take the last name from the row above and combine it with the first name on the current row, such as:

Sample data (two fields, sequential record number and text dump from mainframe)
ID text
2341 12345 Smith 456789456789 12/12/2002
2342 123 456 789 John 345 P16x1440x

Formula in query:
FriendlyName: mid([text],13,12) & “ “ & mid([text] **from line above**, 7,12)
Note: formula show simplified to eliminate iif statement which makes odd numbered ids produce a blank.

As you can see, the second reference to the field [text] has to reference the value with the current (ID–1). How do I do this?

 

[grnzbra]

Since your key fields are sequentially numbered, write a vba function that takes the key field of the current row as a parameter.

In the function subtract 1 from the parameter and do a SQL statement like:

NewKey = Key - 1

SELECT LastName FROM MyTable
WHERE MyTable.KeyField = NewKey

Open the recordset and set the function equal to:
LastNameFunction = rs!LastName



In the query set the name equal to a concatination of the FirstNameField & LastNameFunction(KeyField)

Don't know if it's the best way to do it, but I think it should work.