(Complex) Running Totals in MS Access 2003 Query?

[Spetman]

I’m having trouble using MS Access 2003 to accomplish something that I thought would be relatively easy: I’m trying to create a query [not a report] that will give me the projected balance of an account (such as a 401K account) at each year in the future, which is growing at a fixed rate of X% a year and which also gets additional, lump sum contributions each year.

For example, let’s assume you start with a balance of zero and have these annual contributions, which for the sake of argument are obtainable via a table or query:

Year Contribution (at year end)
2009 $100
2010 $125
2011 $150
2012 $175
…

I’m trying to create another query, using that first query as my data source, which would return the following results, assuming a fixed rate of 10% growth per year:

Year Balance (at year end)
2009 $100
2010 $235 [which is $100*1.10 + 125]
2011 $408.50 [which is $235*1.10 + 150]
2012 $624.35 [which is $408.50*1.10 + 175]
…

I’m not really an Access superhero nor SQL/VB guy, but after surfing the blogs, I have tried hacking around with Public Functions, Global Variables, DSUM, embedded Selects, etc., and I just haven’t been be able to figure out a solution [call me stupid!] Any help or advice would be severely appreciated!

P.S. Like many things in Access, there's probably several ways to skin this cat - I just need one! I think I could pull it off if I could figure out how to embed and execute a SELECT statement within a Public Function, and assign the value to a variable within a DO...LOOP. But I'd take any other ideas too!

[NaftoliGug]

I don't know for sure, but I think you could have a column in your query that is based on an expression, using the DSum function - DSum("contribut ion_amount", "contributions_ table", "year <= year_field") where year_field is the year of the current row. This will aggregate (the D... functions are called domain aggregate functions) the contributions to the account for all years up to the year of the current row. So just write DSum(...) + starting_amount , which could be another field in the query.

[NeoPa]

Ah, a nice interesting problem that fits nicely (if not necessarily obviously) into SQL.

If we assume the data is stored in table tblContribution , then we have :

Table Name=[[U]tblContribution[/U]]
[I]Field;        Type;    IndexInfo[/I]
YearNo;       Numeric; PK
Contribution; Numeric

We will need to link it into itself and calculate the sum effect of each record, bearing in mind the delay in years, and incorporate that total into the main record.

SELECT tC1.Year,
       tC1.Contribution+
       Nz(Sum(tC2.Contribution*1.1^(tC1.Year-tC2.Year))) AS TotContribution
FROM tblContribution AS tC1 LEFT JOIN tblContribution AS tC2
  ON tC1.Year>tC2.Year
GROUP BY tC1.Year,
         tC1.Contribution

[NeoPa]

Amended SQL as I'd incorrectly multiplied the percentages (110%) instead of powering them.

PS. Click on image for a clearer picture.

[Spetman]

NeoPa, your solution works great, and what's even better is that I understand it! Much, much thanks...

[NeoPa]

My pleasure. It was a fun question :)

Kudos for understanding it too. It's not enormous but it takes a little thinking to appreciate what it's doing, particularly to understand why it works when at first glance you would think it had not included all it needed to.