Returning field from a previous record.

  • Thread starter Thread starter Joan
  • Start date Start date
J

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
 
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
 
Thanks Steve. This was a big help.

Joan


Steve Schapel said:
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
Click to expand...
Click to expand...
 
Back
Top