Access get value from previous record

0
votes

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

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

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

enter image description here

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

Your help would be greatly appreciated,
Thank you

4
ms-access
Are Account, FiscalYear and FiscalPeriod stored as text in the database? Just wondering as your screenshot shows them left aligned and FiscalPeriod has a leading 0. – Darren Bartrup-Cook

4 Answers

0
votes

This SQL does the job and returns the table below (with my test data):
enter image description here

SELECT    T1.Account
        , T1.FiscalYear
        , T1.FiscalPeriod
        , T1.ActualBeginngBal
        , (
           SELECT TOP 1 T2.EndingBalance
           FROM   Table1 T2
           WHERE CLNG(T2.FiscalYear & Format(T2.FiscalPeriod,"00")) <
                 CLNG(T1.FiscalYear & Format(T1.FiscalPeriod,"00")) AND
                 T2.Account = T1.Account
           ORDER BY CLNG(T2.FiscalYear & Format(T2.FiscalPeriod,"00")) DESC
           ) AS BeginningBalance
        , T1.EndingBalance
FROM    Table1 T1
0
votes

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.

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;
0
votes

IT WOULD BE BETTER IF YOU USE BeginningBalance in primary table. Because it is only one record belong to one account. it is one to one relationship.

0
votes

How about moving to the previous record, grabbing the value, and returning to the original record.

For example:

Private Sub txtXYZ1_AfterUpdate()

    Dim tmpXYZ As Single
    DoCmd.GoToRecord , , acPrevious
    tmpXYZ = txtXYZ1
    DoCmd.GoToRecord , , acNext
    txtPriorXYZ = tmpXYZ
    txtXYZChange = txtXYZ1 - txtPriorXYZ

End Sub