By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,676 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,676 IT Pros & Developers. It's quick & easy.

(Complex) Running Totals in MS Access 2003 Query?

P: 2
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!
Aug 11 '08 #1
Share this Question
Share on Google+
5 Replies


P: 13
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("contribution_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.
Aug 11 '08 #2

NeoPa
Expert Mod 15k+
P: 31,276
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 :
Expand|Select|Wrap|Line Numbers
  1. Table Name=[tblContribution]
  2. Field;        Type;    IndexInfo
  3. YearNo;       Numeric; PK
  4. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tC1.Year,
  2.        tC1.Contribution+
  3.        Nz(Sum(tC2.Contribution*1.1^(tC1.Year-tC2.Year))) AS TotContribution
  4. FROM tblContribution AS tC1 LEFT JOIN tblContribution AS tC2
  5.   ON tC1.Year>tC2.Year
  6. GROUP BY tC1.Year,
  7.          tC1.Contribution
Aug 11 '08 #3

NeoPa
Expert Mod 15k+
P: 31,276
Amended SQL as I'd incorrectly multiplied the percentages (110%) instead of powering them.

PS. Click on image for a clearer picture.
Attached Images
File Type: jpg Contributions.Jpg (18.0 KB, 473 views)
Aug 11 '08 #4

P: 2
NeoPa, your solution works great, and what's even better is that I understand it! Much, much thanks...
Aug 12 '08 #5

NeoPa
Expert Mod 15k+
P: 31,276
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.
Aug 12 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.