Pull a value from previous record for query

L

Lance

Is there a function that can be used to access a value from a previous record
in a query? I'm trying to build a formula that will pull the data from a
specific field found in the previous record. i.e. field2:
previous_record([field1])

*assuming 'previous_record' were a function

If the query worked correctly, it would reflect:
field1 field2
45 0
64 45
73 64

I appreciate any help!
 
D

Douglas J. Steele

What determines which record is the previous one? If, for example, you've
got a date/time stamp on each record, then the previous one would be
DLookup("MyField", "MyTable", "Timestamp = " & DMax("TimeStamp", "MyTable",
"Timestamp < " & Format(Timestamp, "\#mm\/dd\/yyyy\#"))
 
L

Lance

The hope is that the previous record is the one preceeding given the assigned
sort order. In this case, the query is sorted first by 'aircraft', second by
'date', third by 'time'.

Multiple records could have the same 'aircraft' and 'date', however, no two
records would ever have the same 'aircraft', 'date', AND 'time'. With that
said, each record is unique by a combination of these 3 fields and sorted by
them.

Each record has a 'fuel out' and a 'fuel in'. My goal is to get the 'fuel
in' from the previous record (in sort order) carried into each subsequent
record so that 'previous record fuel in', 'fuel out', and 'fuel in' all three
exist in the same record.

i.e.
Aircraft Date Time Fuel Out Fuel In Previous Fuel In
N1234A 10/1/06 0600 10000 8000 0
N1234A 10/1/06 0700 8000 6000 8000
N1234A 10/1/06 0800 10000 5000 6000

'Previous Fuel In' is the calculated field that reflects the 'fuel in' from
the previous record. This is not part of the core data, thus my attempt to
create a formula to create inside the query.

I appreciate all your suggestions and assistance.

Lance



Douglas J. Steele said:
What determines which record is the previous one? If, for example, you've
got a date/time stamp on each record, then the previous one would be
DLookup("MyField", "MyTable", "Timestamp = " & DMax("TimeStamp", "MyTable",
"Timestamp < " & Format(Timestamp, "\#mm\/dd\/yyyy\#"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Lance said:
Is there a function that can be used to access a value from a previous
record
in a query? I'm trying to build a formula that will pull the data from a
specific field found in the previous record. i.e. field2:
previous_record([field1])

*assuming 'previous_record' were a function

If the query worked correctly, it would reflect:
field1 field2
45 0
64 45
73 64

I appreciate any help!
 
V

Van T. Dinh

See an example at

http://tinyurl.com/ybqcsz

In you case, you need to modify the SubQuery so that the SubQuery only pick
up the related record for the same aircraft.

--
HTH
Van T. Dinh
MVP (Access)



Lance said:
The hope is that the previous record is the one preceeding given the
assigned
sort order. In this case, the query is sorted first by 'aircraft', second
by
'date', third by 'time'.

Multiple records could have the same 'aircraft' and 'date', however, no
two
records would ever have the same 'aircraft', 'date', AND 'time'. With
that
said, each record is unique by a combination of these 3 fields and sorted
by
them.

Each record has a 'fuel out' and a 'fuel in'. My goal is to get the 'fuel
in' from the previous record (in sort order) carried into each subsequent
record so that 'previous record fuel in', 'fuel out', and 'fuel in' all
three
exist in the same record.

i.e.
Aircraft Date Time Fuel Out Fuel In Previous Fuel In
N1234A 10/1/06 0600 10000 8000 0
N1234A 10/1/06 0700 8000 6000 8000
N1234A 10/1/06 0800 10000 5000 6000

'Previous Fuel In' is the calculated field that reflects the 'fuel in'
from
the previous record. This is not part of the core data, thus my attempt
to
create a formula to create inside the query.

I appreciate all your suggestions and assistance.

Lance



Douglas J. Steele said:
What determines which record is the previous one? If, for example, you've
got a date/time stamp on each record, then the previous one would be
DLookup("MyField", "MyTable", "Timestamp = " & DMax("TimeStamp",
"MyTable",
"Timestamp < " & Format(Timestamp, "\#mm\/dd\/yyyy\#"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Lance said:
Is there a function that can be used to access a value from a previous
record
in a query? I'm trying to build a formula that will pull the data from
a
specific field found in the previous record. i.e. field2:
previous_record([field1])

*assuming 'previous_record' were a function

If the query worked correctly, it would reflect:
field1 field2
45 0
64 45
73 64

I appreciate any help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top