Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest Calc

[jrub]

Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest Calc

Howdy All,

I have three columns, StartingBalance, AnnualContribution, and InterestIncome.

I would like to build a query that for the first row, uses a previously defined elsewhere starting balance, annual contribution, and interest income. Just for the first row, these values are set by the user.

The second row, and until the end of the query, it automatically calculates the new starting balance value based on the previous row data.

For example, row 2's starting balance is the sum of the previous row's starting balance, annual contribution, and interest income.

This forces row 2's annual contribution and interest income to a calculated value new values based on the new starting balance value (the sum of the previous row's starting balance, annual contribution, and interest income).

This is then repeated until the end of the queried values.

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Ahh, one of my favorite topics--ordering. Order exists only when you tell it to exist.
There is no 'first', 'previous', 'next', or 'last' rows in your data, unless you tell it how to determine all those.

You stated you have 3 columns, none of which seems to be one on which you can use to order your data. So how do you order your data? Do you have a date field in the data source? A transaction ID?

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Ah yes, there is no order, my apologies.

Rows are "technically" ordered by year, the query looks for years and returns the range in the column "StudyYears", and the query data is sorted by this column, increasing.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Is the query you posted the screen shot of the basis of this dataset you first mentioned? If so, can you run it and post a screenshot of the data it displays? I only need to see 15 or 20 rows of data.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Yes, it is the basis.

The SQL argument is the following:

SELECT AnalysisYears.StudyYears, [Funding Plan Ideal Balances].UnadjustedIdealBalance, [Funding Plan Ideal Balances].InflationAdjustedIdealBalance, ReserveParameters.InvestmentRate AS InvestRate, Nz(IIf([StudyYears]=[ReserveParameters]![StartingBalanceYear],[ReserveParameters]![StartingBalance],ELookup("[AnnualContribution]","GenerateFundingPlanData","[StudyYears]=" & [StudyYears]-1)),0) AS StartBalance, Format([ReserveParameters]![CashFlowStartingContribution]*(1+[ReserveParameters]![InvestmentRate])^([StudyYears]-[ReserveParameters]![StartingBalanceYear]),"Currency") AS AnnualContribution, [InvestRate]*([StartBalance]-[Inflation_Adjusted_Expenditures]/2+[AnnualContribution]/2) AS InterestIncome, [Funding Plan Summary Expenditures].Inflation_Adjusted_Expenditures, [Funding Plan Summary Expenditures].Expr1 AS UnadjustedExpenditures
FROM ReserveParameters, [Funding Plan Summary Expenditures] INNER JOIN (AnalysisYears INNER JOIN [Funding Plan Ideal Balances] ON AnalysisYears.StudyYears = [Funding Plan Ideal Balances].StudyYears) ON [Funding Plan Summary Expenditures].CEY = AnalysisYears.StudyYears
ORDER BY AnalysisYears.StudyYears;

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Also,

I was using an if statement to determine if the startingbalance record was the first row or not.

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

If so, can you run it and post a screenshot of the data it displays? I only need to see 15 or 20 rows of data.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Hi Plog,

Thanks again for your help.

I posted the sql statement and a screen shot, is this sufficient?

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

The screenshots not showing on this page, but I was able to get its url and see it in a new window. Give me a few minutes.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Weird, I embdded the image into the post above.

The link is http://s9.postimage.org/4xp6cg5zi/Untitled2.jpg

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Your query is doing what you want it to. I don't understand what you need now.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

The first row is showing the right data for startbalance, annualcontribution and interestincome.

The second row should show:

488,098 as the next start balance (the sum of 404,585 + 61,740 +,21,773). This is the previous record's values.

The annual contribution is always a calculated value not involving the starting balance.

The interestincome is a calculated value based on the start balance of record's row.


So essentially, for everything but the first row, I need the startbalance to be the sum of the previous row's startbalance, annualcontribution and interestincome.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Essentially a cumulative sum with additional data.

I found this link: http://itdeveloperzone.blogspot.ca/2011/03/cumulative-sum-in-sql-server.html

But I am a noob and can barely get by with the existing SQL for the life of me.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Could DSum work with this?

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

My suggestion would be to add a field called EndBalance to every record which will be the Start Balance of the next record. Then you can use a Dlookup to get that EndBalance.

Can you post your actual database?

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

plog,

I attached the file.

I tried the EndBalance approach. When I include the dlookup statement in the startbalance expression for the query, the whole things crashes (literally).

Any help is appreciated.

 

[jrub]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Anyone out there who could contribute?

Help me, Obi-Wan Kenobi. You're my only hope.....

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

I only have Access 2007 at work. I will have a look at this monday unless you can convert it to a .mdb

 

[plog]

Re: Add Three Previous Row Value Together to Make Next Row Value -Balance & Interest

Second, you have too much crap in there. What am I looking at? First, it give me an error when I open it about a bad or missing reference to a file. I think your query is the 'GenerateFundingPlanData' but I can't open it in design view because its missing an underlying data source.