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!
5 5287
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.
NeoPa 32,556
Recognized Expert Moderator MVP
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=[tblContribution]
- Field; Type; IndexInfo
-
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 32,556
Recognized Expert Moderator MVP
Amended SQL as I'd incorrectly multiplied the percentages (110%) instead of powering them.
PS. Click on image for a clearer picture.
NeoPa, your solution works great, and what's even better is that I understand it! Much, much thanks...
NeoPa 32,556
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tony Williams |
last post by:
I have a database that hold s figures for each quarter. I want to create a
query whereby the user can ask for the running total of the data upto the
quarter they enter into a prompt. The database...
|
by: BerkshireGuy |
last post by:
I have the following code:
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined,
intNumOfWasted,...
|
by: Steve_s |
last post by:
Hi
I am having problems totalling a particular field in a subform.I am
using Access 2003. The subform is based on a query which selects
records with various financial information. I have 5 text...
|
by: Jana |
last post by:
Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. ...
|
by: mochatrpl |
last post by:
I am looking for a way to make a query / report display the running
average for total dollars.
I have already set up a query to provide totals dollars per day from
which a report graphly shows...
|
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with...
|
by: elias.farah |
last post by:
Hello Everyone,
I'm having some very weird behavior on a couple of Access forms. (Not
all forms, just some of them).
The forms have been working for years, under Access XP/2003 etc, and
last...
|
by: BillShaw |
last post by:
Using Access 2003 I am trying to construct a query to provide data for a stacked column graph to show cumulative sales figures month on month individually by salesman. Eg. a column will represent a...
|
by: colin spalding |
last post by:
How do I design a totals query that instead of the following result
YearOfAcc PremiumGBP
2004 9,142,306.95
2004 1,481,153.21
2005 11,981,987.85
2006 20,653,195.20
2006 ...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |