访问从以前的记录中获取值 [英] Access get value from previous record

问题描述

我有一个 Access 查询，内容如下

I have an Access query with the following

GL_A.Account, 
GL_P.FiscalYear, 
GL_P.FiscalPeriod, 
GL_P.BeginningBalance, 
GL_P.DebitAmount, 
GL_P.CreditAmount, 
[BeginningBalance]+([DebitAmount]-[CreditAmount]) AS EndingBalance

问题在于 BeginningBalance 只有一月(FiscalPeriod 1)的值.

The problem is that BeginningBalance only has values for January (FiscalPeriod 1).

我需要一个来自上个月的新字段 ActualBeginngBal EndingBalance(一月除外)

I need to have a new field ActualBeginngBal which comes from the previous month EndingBalance (Except January)

注意:有很多帐户#，但每个帐户每个 FiscalPeriod/FiscalYear 只有 1 条记录

Note: there are many account #'s but each account only has 1 record per FiscalPeriod/FiscalYear

非常感谢您的帮助，
谢谢

Your help would be greatly appreciated,
Thank you

推荐答案

看看这是否有帮助.将表的第二个副本放入查询中，在 Account 和 FiscalYear 上加入第一个副本，但不在 FiscalPeriod 上.然后可以从表的第二个副本计算 ActualBeginningBalance 并带有约束，以仅选择 FiscalPeriod <;第一个表中的 FiscalPeriod.注意 - 您可能会得到一月份的空结果，您可能需要将其转换为 0.

See if this helps. Put a 2nd copy of the table in the query, joined to the 1st copy on Account and FiscalYear but not on FiscalPeriod. Then the ActualBeginningBalance can be calculated from the 2nd copy of the table with a constraint to select only FiscalPeriod < the FiscalPeriod from the 1st table. Note - you may get a null results for January, which you may need to convert to a 0.

好吧，它有点复杂 - 我最终使用了类似于另一个响应的子查询，但计算了 EB 而不是尝试从表中提取它

OK, it's a bit more complicated - I did end up using a subquery similar to the other response, but calculated the EB instead of trying to pull it from the table

SELECT Ledger.Account, Ledger.FiscalYear, Ledger.FiscalPeriod, 
[BeginningBalance]+
       IIf([FiscalPeriod]<>"01",
             (select sum(T.BeginningBalance+T.DebitAmount-T.CreditAmount) 
             from Ledger T 
              where T.account=Ledger.account and T.FiscalYear=Ledger.FiscalYear and T.FiscalPeriod<Ledger.FiscalPeriod)
        ,0) 
       AS ActualBeginningBalance, 
Ledger.DebitAmount AS DebitAmount, 
Ledger.CreditAmount AS CreditAmount, 
[ActualBeginningBalance]+[DebitAmount]-[CreditAmount] AS EndingBalance
FROM Ledger;

这篇关于访问从以前的记录中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！