引用 MS 访问查询中的先前记录 [英] referring to a previous record in MS access query

问题描述

我很难在 MS 访问查询中引用以前的记录.我知道 Dlockup 但问题是:我想使用 [TransactionID] 字段引用事务表中的前一条记录，但该表中的事务属于客户表中的许多客户.因此，如果我只是使用 [TransactionID] 字段要求事务表中的先前记录，它只会给我先前的记录，即使它属于另一个客户并且是我不想要的.我现在用这个

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)

但我不知道如何添加一个部分，说只有在前一个记录属于同一个 [CustomerID] 时才这样做?！就像我想要的东西:

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]

或者喜欢

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

非常感谢您的帮助，先感谢您，大卫.

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

推荐答案

您正在寻找属于该客户的最大 TransactionID，即低于当前的:

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

然后将该结果输入到您的原始 DLookUp 调用中:

Then feed that result into your original DLookUp call:

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

这篇关于引用 MS 访问查询中的先前记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！