By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,560 Members | 1,538 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,560 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

Expert Mod 15k+
P: 31,662
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

Expert Mod 15k+
P: 31,662
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, 499 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

Expert Mod 15k+
P: 31,662
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.