CODEWITHSUNDEEP
sqlms-access
SSK
SSK

Reputation: 803

How to take a cumulative previous year record?

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?

Upvotes: 1

Views: 133

Answers (3)

smn_onrocks
smn_onrocks

Reputation: 1342

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;

Upvotes: 0

Barranka
Barranka

Reputation: 21047

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)

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

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;

Upvotes: 0

Related Questions