How to take a cumulative previous year record?

Question

In MS Access, I have a table like below:

FY    Percent   
2015    5%  
2016    5%  
2017    5%  
2018    5%  
2019    5%  
2020    5%

Now I want to add a calculated row and that row should be calculated as shown below:

FY      Calculated
2015    P * 1 (Multiply the value by 1 for first year)
2016    P * 2015 Calculated value (the above value)
2017    P * 2016 Calculated Value
2018    P * 2017 Calculated Value
2019    P * 2018 Calculated Value
2020    P * 2019 Calculated Value
2021    P * 2020 Calculated Value

How do I query that?

As far as I know, there's no way to do this in pure Access SQL... can you (are you willing to) use a VBA procedure for this? — Barranka, Dec 10 '13 at 22:32
No Barranka, i know it is easy to do in VBA or by Excel Formulas — SSK, Dec 10 '13 at 22:34
Hi @Barranka if it is not at all possible with plain query.. i have to go with MS Access VBA. Can you help me with that please — SSK, Dec 11 '13 at 14:25

score 1 · Accepted Answer · edited May 23 '17 at 12:29

There's no way to do this in plain Access' SQL dialect. So, VBA is the choice.

You will need to create a table to hold the values. Let's say your output table is something like this:

Table: tbl_output

FY            Integer
Calculated    Double

The VBA code:

public sub fillCalculatedValues()
    Dim db as DAO.Database, rsIn as DAO.RecordSet, rsOut as DAO.RecordSet
    Dim strSQL as String
    Dim value as Double, fy as Integer, i as Integer

    ' Connect to the current database
    Set db = currentDb()

    ' Get the values from your input table (let's say it is called tbl_in) in read only mode
    strSQL = "SELECT * FROM tbl_in ORDER BY FY"
    rsIn = db.OpenRecordSet(strSQL, dbOpenDynaset, dbReadOnly)

    ' Initialize your output table
    strSQL = "DELETE FROM tbl_output"
    DoCmd.RunSQL strSQL

    ' Open the output table (allowing edits)
    rsOut = db.OpenRecordset("tbl_output", dbOpenDynaset, dbEditAdd)

    ' Read the input row by row, and insert a new row in the output
    with rsIn
        .moveFirst
        i = 1
        value = 1
        do
            fy = ![FY]     ' Read the field FY from rsIn
            ' Insert the new row in the output table
            rsOut.addNew
                rsOut![FY] = fy
                rsOut![calculated] = value
            rsOut.update
            ' Update the calculated value
            value = value * ![percent]
            ' Advance one row
            .moveNext
        loop until .EOF  ' Loop until you get to the end of the table
    end with
    ' Close everything
    rsOut.close
    rsIn.close
    db.close
end sub

This is just an example. You should modify it to fit your specific needs

Hope this helps.

P.S.:

A little easter egg for you: The Ten Commandments of Access
If you are willing to move up from Access to a more robust RDBMS, I suggest you take your chances with MySQL. There are some tricks you can use in MySQL to do this sort of things. You can take a look to this post to know how to make a cummulative sum over a set of rows in MySQL, and fit it to your needs (after all, you are making a cummulative product)

Fionnuala · Answer 2 · 2013-12-11T16:30:50.000

0

I am by no means sure what you mean, perhaps

SELECT t.FY, 
     t.Calculated, 
     Nz((SELECT Max(CPercent) 
         FROM tFY 
         WHERE FY< t.FY),1) * Percent AS [Last Value]
FROM tFY AS t

Then, re comment

SELECT t.FY,
      (SELECT Sum(Percent) FROM tFY WHERE FY<= t.FY) AS [Calc Value]
FROM tFY AS t;

edited Dec 11 '13 at 16:30

answered Dec 10 '13 at 22:48

Fionnuala

90,370
7
114
152

Thaks Remou, not looking for this.. actually the max(Percent) is the calculated max(value). i have edited my question to make it easier.. please check if it is clear now – SSK Dec 11 '13 at 14:10

score 0 · Answer 3 · answered Jan 20 '14 at 08:16

0

try this out may be this could help you

select fy,Percent as curr_percent, sum(Percent) over (order by fy) as cal_field from tbl_cal_field;

answered Jan 20 '14 at 08:16

smn_onrocks

1,282
1
18
33

How to take a cumulative previous year record?

3 Answers3