CODEWITHSUNDEEP
ms-accessms-access-2010
David
David

Reputation: 1

referring to a previous record in MS access query

I have a very Hard Time referring to a previous record in MS access query. I know about the Dlockup but the problem is: I want to refer to a previous record in the transaction table using [TransactionID] field, BUT transactions in that table is belong to many Customers in the Customers table. So if I just ask for a previous record in the transaction table using the [TransactionID] field it will just give me the previous record even if it belongs to another customer and that what I don't want. I use this right now

Expr1: DLookUp("[TheFieldThatIWantItsPreviousValue]",
"TheQueryThatHavingTheTwoTablesTogether","[TransactionID]=" & [TransactionID]-1)

But I don't know how to add a part that say do that only if the previous record belongs to the same [CustomerID]?! Like I want something like:

Expr1: DLookUp("[TheFieldThatIWantItsPreviousValue]",
"TheQueryThatHavingTheTwoTablesTogether","[TransactionID]=" & [TransactionID]-1) where [CutomerID] = [CutomerID]

Or Like

Expr1: DLookUp("[TheFieldThatIWantItsPreviousValue]",
"TheQueryThatHavingTheTwoTablesTogether","[TransactionID]=" & [TransactionID]-1 and [CutomerID] = [CutomerID])

I Appreciate your help very much, Thank you in advance, David.

Upvotes: 0

Views: 4001

Answers (1)

Andre
Andre

Reputation: 27634

You are looking for the maximum TransactionID belonging to that customer, that is lower than the current one:

DMax("TransactionID", "TransactionTable", 
     "CustomerID = " & CustomerID & " AND TransactionID < " & [TransactionID])

Then feed that result into your original DLookUp call:

DLookUp("TheFieldThatIWantItsPreviousValue]", "TheQuryThatHavingTheTwoTablesTogether", 
    "[TransactionID]=" & DMax(... see above ...))

Upvotes: 2

Related Questions