访问从以前的记录中获取价值 [英] 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屋！