Pull a value from previous record for query

[Guest]

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!

 

[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\#"))

 

[Guest]

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

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)

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!

 

[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)

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

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)

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!

 

[Guest]

Previous Date & Sum of Month/Year to-date using a correlated subquery
http://www.accessdb.info/content/view/126/43/