Returning field from a previous record.

[Joan]

Hi,
Could someone tell me if there is a way to return a field value from a
previous record with WHERE criteria? What I need is the second to the last
Invoice recorded for a certain store. However, both invoices and
adjustments are recorded in the Transaction table where the distinguishing
field is Type. For invoices, [Type] = "INV" and for adjustments, [Type] =
"ADJ". Each record in the transaction table has a Date field. However,
since it is the second to the last record, the DMax() function using this
Date field will not work.

Joan

 

[Steve Schapel]

Joan,

You will need to do this in two steps. These two steps can be
incorporated within a singel query, but for simplicity, try this...
1. Make a query based on your Transactions table, select out the INV
records and the Store criteria, sort Descending on the Date field, and
use the TOP 2 predicate (enter 2 in the Top Values box in the query
design toolbar).
2. Apply your DMax() or DMin() function (depending on you you sort
these two records) to end up with the second last invoice.

- Steve Schapel, Microsoft Access MVP

 

[Joan]

Thanks Steve. This was a big help.

Joan

Joan,

You will need to do this in two steps. These two steps can be
incorporated within a singel query, but for simplicity, try this...
1. Make a query based on your Transactions table, select out the INV
records and the Store criteria, sort Descending on the Date field, and
use the TOP 2 predicate (enter 2 in the Top Values box in the query
design toolbar).
2. Apply your DMax() or DMin() function (depending on you you sort
these two records) to end up with the second last invoice.

- Steve Schapel, Microsoft Access MVP

Hi,
Could someone tell me if there is a way to return a field value from a
previous record with WHERE criteria? What I need is the second to the last
Invoice recorded for a certain store. However, both invoices and
adjustments are recorded in the Transaction table where the distinguishing
field is Type. For invoices, [Type] = "INV" and for adjustments, [Type] =
"ADJ". Each record in the transaction table has a Date field. However,
since it is the second to the last record, the DMax() function using this
Date field will not work.

Joan