Is there any way to run a loop within a query on access 2000?

[Crimsonwingz]

Need to calculate a sum based on a number of factors over a period of
years. I can use formula ^x for some of it, but need totals to carry
over in the sum and have only been able to do this thus far with a loop
in a form.

Basically, I have key sums

Current savings
Current Salary
Current deposit amount
Current Interest

I have to get cost of living added to salary each year
New Salary means different dollar amount going into savings
Differing deposits from year to year means differing interest
All of this, added to the starting balance, and than started all over
again for X number of years.

A table holds the defaults (interest, how many years, cost of living
increase)

A form works fine on a one by one basis, but I have to make this work
for a couple hundred entries, to be printed in a report.

is it even possible? Where can I look for help?

Jeff

[PC Datasheet]

You need to use a totals query grouped on Year and Individual. You will
probably need some calculated fields in the query as well. Click on the
Sigma (looks like E) button on the toolbar at the top of the screen. You can
then change Group By under any field in the query to an expression. Explore
and see what is available here.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Crimsonwingz" <je**@goldwingz.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Need to calculate a sum based on a number of factors over a period of
years. I can use formula ^x for some of it, but need totals to carry
over in the sum and have only been able to do this thus far with a loop
in a form.

Basically, I have key sums

Current savings
Current Salary
Current deposit amount
Current Interest

I have to get cost of living added to salary each year
New Salary means different dollar amount going into savings
Differing deposits from year to year means differing interest
All of this, added to the starting balance, and than started all over
again for X number of years.

A table holds the defaults (interest, how many years, cost of living
increase)

A form works fine on a one by one basis, but I have to make this work
for a couple hundred entries, to be printed in a report.

is it even possible? Where can I look for help?

Jeff

[Crimsonwingz]

Never used sigma before, and help doesnt show much about it. What does
this do?

Trying to think of a better example here...

Employee is 35. Has 1000 in savings. Makes 25000/year. Deposits 10%
of her salary. Getting 5% return on her savings. Gets a 5% raise per
year.
Need to see where he is at when age 50 (15 years)

next year, savings is 1000+ 2500 (deposit) + 175 interest. Pay
increases to 26250. Repeat 14 more times, basically.

How can this be done with Sigma? Too many questions, right? Everytime
I think I am getting decent at this fun stuff, I get thrown for a loop.

Thanks for the response!!
Jeff

[PC Datasheet]

Now that you have defined the problem more specifically, a totals query is
not the solution. This is a finance problem; look at financial functions in
the Help file. You will need to use more than one to get your answer.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Crimsonwingz" <je**@goldwingz.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Never used sigma before, and help doesnt show much about it. What does
this do?

Trying to think of a better example here...

Employee is 35. Has 1000 in savings. Makes 25000/year. Deposits 10%
of her salary. Getting 5% return on her savings. Gets a 5% raise per
year.
Need to see where he is at when age 50 (15 years)

next year, savings is 1000+ 2500 (deposit) + 175 interest. Pay
increases to 26250. Repeat 14 more times, basically.

How can this be done with Sigma? Too many questions, right? Everytime
I think I am getting decent at this fun stuff, I get thrown for a loop.

Thanks for the response!!
Jeff

[Crimsonwingz]

I know the formula's to get the results, but not how to do them in such
a way that I can run all of the employees at once. Would running a
form with a dbrecordset (not sure how to use this, either) in a form to
modify new fields in the table work? This project is driving me nuts,
and I seem to be going in circles.

Jeff

[PC Datasheet]

Let's make sure you have a good design of your tables and then try and do
the calculations in a query step by step.
Tables-----
TblEmployee
EmployeeID
FirstName
MI
LastName
DOB
InitialSavings
StartingSalary
PercentOfSalaryDeposit
PercentReturnOnSavings
PercentRaisePerYear

TblNumbers
NumberOfYears

TblNumbers will be used in the query to set the number of years for your
calculations. For now, fill the table with 0 to 100 consecutively. Year 0
will be your initial data.

When the above are completed, you can start building your query. Include
both tables with no joins. Concatenate FirstName, MI and LastName to get the
employee name. Pull down NumberOfYears. Run your query. You will get one
hundred lines with the Employee name and NumberOfYears 1 to 100. This is how
you control the number of years for your calculations. Set the criteria for
NumberOfYears as <=15 and run your query. Now you see how that works.

Next you need to get the employee's age for each year into the query. Here's
the calculation:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd")

Format(Date(), "mmdd"), 1, 0) Put this in a blank field.

Next put this expression in a blank field:
AgeForCalculation:[Age] + [NumberOfYears]
Run the query and see what you have now.

Next you need to get the salary for each year. This is a future value
calculation and you say you know the formula. In the formula where you have
the number of years, use the [NumberOfYears] field. Put your equation in a
blank field in the query just like you did the age calculation.

Finally, in similar fashion, you need to work out the formula that gives you
the amount in savings each year.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Crimsonwingz" <je**@goldwingz.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com... I know the formula's to get the results, but not how to do them in such
a way that I can run all of the employees at once. Would running a
form with a dbrecordset (not sure how to use this, either) in a form to
modify new fields in the table work? This project is driving me nuts,
and I seem to be going in circles.

Jeff

[pietlinden]

> Trying to think of a better example here...

Employee is 35. Has 1000 in savings. Makes 25000/year. Deposits 10% of her salary. Getting 5% return on her savings. Gets a 5% raise per year.
Need to see where he is at when age 50 (15 years)

next year, savings is 1000+ 2500 (deposit) + 175 interest. Pay
increases to 26250. Repeat 14 more times, basically.

How can this be done with Sigma? Too many questions, right? Everytime I think I am getting decent at this fun stuff, I get thrown for a loop.
Thanks for the response!!
Jeff

Feel free to mangle this as necessary...

Option Compare Database

'Employee is 35. Has 1000 in savings. Makes 25000/year. Deposits 10%
'of her salary. Getting 5% return on her savings. Gets a 5% raise per
'year.
'Need to see where he is at when age 50 (15 years)

'next year, savings is 1000+ 2500 (deposit) + 175 interest. Pay
'increases to 26250. Repeat 14 more times, basically.

Public Function CalcRetirement(ByVal intAge As Integer, curSavings As
Currency, curSalary As Currency, curAnnualRaise As Currency,
curReturnOnSavings As Currency) As Currency

Const intRetirement As Integer = 50
Dim intYear As Integer

CalcRetirement = curSavings

For intYear = intAge To intRetirement
CalcRetirement = (1 + curReturnOnSavings) * curSavings
CalcRetirement = CalcRetirement + 0.1 * curSalary
curSalary = curSalary + curAnnualRaise
Next intYear
End Function

You can create a FOR... NEXT loop inside your custom function to loop
and aggregate savings or whatever. (Just put an aggregating expression
inside the loop - something like CalcRetirement=CalcRetirement + (some
function or value)

Then you can do all the bits and pieces separately inside the loop.
(otherwise you might go nuts trying to get and keep it all straight in
your head.) Another advantage to that is that you can insert a
Debug.Print... and then print intermediate values. (Handy for debugging
and making sure your function works - just do a few small examples to
prove that your function is working, and then you can just call it in a
query.)

hope this points you in the right direction. Alternatively, if you
know how to do all this stuff in Excel, you *can* include a reference
to the Excel library and just use the built-in functions of Excel...

[Crimsonwingz]

Do I use this in a form, or in a query? And glad to see you sympathise
with the headache I have been getting (grin)

Jeff

[pietlinden]

I'm getting a little lost. Do you need to show the "steps" to getting
to your goal? Okay, say you pass a bunch of values to your
CalcRetirement function. Do you need Year(1)... Year(n) or just the
total? If all you need is the total, you should be able to create a
query, throw in the fields you want to see in your report, and then the
function with the parameters passed to it, and you should be fine.

If you want to show the steps (Total Retirement in Year 1 = X, up to
Year n and then show a total), then you may need to write the values to
a temporary table and do a running sum or something like that in your
report. But see if someone smarter than I can answer your question....

[Crimsonwingz]

Sorry for the confusion. I am not explaining it very well, I know. I
don't need to show the steps, but I will need to show three dates,
which are set in the default table. I used 50 as an example, but it
might be 55,60, and 65, or 50,60,70, etc.

When all is said and done, I need to print a report of each customer
showing current info, and projections of the 3 ages. I believe that
the for next loop on a command on a form will do what I need, but I
imagine I will have to run a loop within the loop to get the results
for the 3 seperate years.

IE John Smith - 10000/yr, 5% raise per year, 10% deposit /yr, 5000 in
savings, 5% return on savings.
Year 5 12762 salary, 607.75 deposited, 9572.11 in
savings
Year 10 XXXXXX XXXXX
XXXXXXXXXXXX
Year 15 XXXXXX XXXXX
XXXXXXXXXXXX
I have a single form setup that runs the loops for each of the year and
gets the totals. The problem with this being I have to go through each
record, click the calculate button, and print the report before moving
to the next record.

I thought a query would be nice... open a report attached to the query
and let 'er print. But can't figure out how to do the loops necessary
to get the results.

With some of the previous thoughs here, my next idea was to set up a
form that attached to the defaults table (in case the defaults need to
be changed), with a command button to run the function/loops and print
the report. I am pretty sure that a for next loop with a loop until
inside that runs the age progressions will get my needed results. I
haven't learned the for next loop properly yet. The info you posted
gave me a little insight, but I think I have a long way to go.

Is this any clearer, or am I getting muddier as I go? I know I am
missing a relatively simple key point, but can't seem to find it.
Thanks again everyone for your help on this issue!

[Mike Preston]

On 14 Mar 2005 05:28:44 -0800, "Crimsonwingz" <je**@goldwingz.com>
wrote:

Sorry for the confusion. I am not explaining it very well, I know.
This is usually a sign that you don't really understand the issues
involved in doing the actual calculations. You might want to plot
this out using Excel so you can see what you are after.
I
don't need to show the steps, but I will need to show three dates,
which are set in the default table. I used 50 as an example, but it
might be 55,60, and 65, or 50,60,70, etc.
Here you use ages (55, 60, etc.). Below you use number of years from
current (5, 10, 15, etc.). You need to decide which it is you are
doing, because it is tough to do BOTH! ;-)
When all is said and done, I need to print a report of each customer
showing current info, and projections of the 3 ages. I believe that
the for next loop on a command on a form will do what I need, but I
imagine I will have to run a loop within the loop to get the results
for the 3 seperate years.
No, you don't. All you need is the correct formulas.
IE John Smith - 10000/yr, 5% raise per year, 10% deposit /yr, 5000 in
savings, 5% return on savings.
Year 5 12762 salary, 607.75 deposited, 9572.11 in
savings
Well, the above is a bit confusing. It implies that his $10,000/year
salary is the salary at the BEGINNING of year one and that in the real
"first year" his compensation is increased by the 5% factor, and so
on, until in the 5th year, his compensation is $12,762.

This is an abnormal way of showing the calculations, but it may be
exactly what you want. I can't tell.

It would normally display as $10,500 salary in the first year and
$12,762 salary in the 5th year.

Unless, of course, the salary in the first year is really $10,000, in
which case salary in the 5th year is actually $12,155.06.

Your "$607.75" isn't 10% of anything. It is 5% of the salary from the
year before ($12,155.06 * 0.05). Is that really what you want?

I'm sure your $9,572.11 is similarly in need of clarification.
Year 10 XXXXXX XXXXX
XXXXXXXXXXXX
Year 15 XXXXXX XXXXX
XXXXXXXXXXXX
I have a single form setup that runs the loops for each of the year and
gets the totals. The problem with this being I have to go through each
record, click the calculate button, and print the report before moving
to the next record.

I thought a query would be nice... open a report attached to the query
and let 'er print. But can't figure out how to do the loops necessary
to get the results.
Don't need loops. Need financial functions.

If you are looking for the accumulation of the initial $5,000 balance,
that is simple:

OriginalBalance * ((1 + interest_rate) ^ years)

If you are looking for the accumulation of a series of n payments,
each made at the end of the year (not a very good assumption, by the
way), with interest at i% per annum, the formula is:

Accumulation = payment * ([(1 + i%) ^ n] - 1 / i)

For example, the accumulation of $500 made 5 years in a row, at 5%
interest is: $500 * ([(1.05)^5] - 1 / 5%) = $2,762.82

But you want your "payment" to increase each year at the rate that
salary is increasing. So what you want is the accumulation of an
initial payment of $500, increasing j% each year, until the date that
the 5th payment is made.

If j% = i% (in your example, the payment is increasing at 5% per year
and the investment earnings are also being credited at 5% per year)
the formula becomes incredibly simple:

Accumulation = payment * n * ((1 + j%) ^ (n -1))

For example, the accumulation of an initial payment of $500,
increasing at 5% per year, made for 5 years, where the 5th payment is
$500 increased 4 times by 5%, using 5% interest is:

$500 * 5 * ((1.05)^(4)) = $3,038.77

It gets a bit more interesting when the increase rate (j%) does not
equal the investment earnings rate (i%). When that happens, you first
determine j' as:

j' = {[(1+i)/(1+j)] - 1} * 100

For example, if the increase rate (j) is 8% and the investment
earnings rate (i) is 5%, j' is then:

j' = {[(1.05)/(1.08)] - 1} * 100 = -2.7777778% (yes, NEGATIVE
2.7777778%)

Now that you have j', the formula is:

Accumulation = payment * ((1 - (1+j')^-n)/(j'/(1+j'))) * ((1+i) ^
(n-1))

For example, the accumulation of an initial payment of $500 with each
subsequent payment increasing by 8% until the 5th payment is made, at
5% interest is: $500 * ((1 - (1+(-2.7777778%))^
-5)/(-2.7777778%/(1-2.7777778%))) * ((1.05) ^ (5-1)) = $3,217.44

When the increase in the payment is less than the assumed investment
return, you use the same formula, but it doesn't look so funny,
because the concocted interest rate (j') doesn't turn out to be
negative.

If you want to adjust the formulas so that they recognize payments as
of the beginning of the year, adjust each by multiplying the result by
1 + i, where i is the investment return assumption.

I might suggest that you use the average of the two (or, at the least,
adjust by (1+i)^0.5) because it is unlikely that in the real world you
are going to have all payments made precisely at the beginning of the
year or at the end of the year.

I won't be able to check back in the next couple of days. Hopefully
this gives you enough to crystallize your thinking and gives you
enough to suss out the formulas you really want to use.

It is quite a bit of effort, but it avoids all loops. Check a few
against results in excel, though, because it is incredibly easy to
misplace the parenthesis in that long formula.

mike
With some of the previous thoughs here, my next idea was to set up a
form that attached to the defaults table (in case the defaults need to
be changed), with a command button to run the function/loops and print
the report. I am pretty sure that a for next loop with a loop until
inside that runs the age progressions will get my needed results. I
haven't learned the for next loop properly yet. The info you posted
gave me a little insight, but I think I have a long way to go.

Is this any clearer, or am I getting muddier as I go? I know I am
missing a relatively simple key point, but can't seem to find it.
Thanks again everyone for your help on this issue!

[jimfortune]

Mike Preston wrote:

Don't need loops. Need financial functions.

I agree. Many of the financial calculations such as those needed to
keep track of an annuity can be done with standard Present Value or
Future Value equations. Although Piet's solution clarified the problem
and can be modified to give you what you want, if many calls are to be
made to the function it is better to get the value with a single
computation. One way to find out how the financial calculations are
derived and how to deal with some new twists is to read Section 7.3
"Solving Recurrence Relations" from:

Discrete Mathematics
Jerrold W. Grossman
ISBN 0-02-348331-8
Macmillan Publishing Co. 1990

The recurrence relations occur naturally when the calculation for a
year is based on the calculation(s) from the previous year(s).

James A. Fortune

[Crimsonwingz]

Normally I dont get anywhere near this level of math functions... and
it shows in my understanding. Going to start back at it this afternoon
and see if I can start to break it down. Even when using his formula I
am getting garbage. Figure I will give myself til this weekend, and if
I can't gain ground will have to tell the boss I can't complete the
project. Everyone has been great helping me, but I when it comes down
to it, if I can't understand it, I am going to have a tough time
learning it.

Thanks for the help everyone
Jeff

[PC Datasheet]

Jeff,

I am in business to provide customers with a resource for help with
Microsoft Access, Excel and Word
applications. You can view my website at www.pcdatasheet.com. If this
project is critical to you, I can get it done for you. If you would like my
help, cntact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Crimsonwingz" <je**@goldwingz.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Normally I dont get anywhere near this level of math functions... and
it shows in my understanding. Going to start back at it this afternoon
and see if I can start to break it down. Even when using his formula I
am getting garbage. Figure I will give myself til this weekend, and if
I can't gain ground will have to tell the boss I can't complete the
project. Everyone has been great helping me, but I when it comes down
to it, if I can't understand it, I am going to have a tough time
learning it.

Thanks for the help everyone
Jeff

[jimfortune]

Crimsonwingz wrote:

Normally I dont get anywhere near this level of math functions... and
it shows in my understanding. Going to start back at it this afternoon and see if I can start to break it down. Even when using his formula I am getting garbage. Figure I will give myself til this weekend, and if I can't gain ground will have to tell the boss I can't complete the
project. Everyone has been great helping me, but I when it comes down to it, if I can't understand it, I am going to have a tough time
learning it.

Thanks for the help everyone
Jeff

Let's see if I can clarify the problem a little further :-).

Let Y = Salary and dblR = Raise Percentage (.05)

Y(0) = 25000
Y(j+1) = Y(j) * (1 + dblR)

So Y(j) = Y(0) * (1 + dblR) ^ j (Eqn 1) should give the salary values
for each year. Using j = 50 - 35 would give the salary at age 50 if
year 0 is age 35.

Let X = Savings, dblS = Savings Percentage (.10) and dblI = Interest
Percentage (.05)

X(0) = 1000

Savings = Last Year's Saving + Amt Deposited + interest

The way you have your example stated it amounts to:

X(j+1) = [X(j) + Y(j) * dblS] * (1 + dblI) (Eqn 2)

E.g., X(1) = [X(0) + Y(0) * dblS] * (1 + dblI) = (1000 + 2500) (1 +
..05) = 3500 * 1.05 = 3675

So the recurrence relation looks something like X(j+1) = (1 + dblI) *
X(j) + f(j)

Solving that involves the section on non-homogeneous recurrence
relations and solving for the homogeneous part along with finding a
particular solution to the non-homogeneous part. That effort might not
be worth the performance gain so you could use Eqns 1 and 2 as is while
looping the years as Piet suggested. Note that savings accounts are
usually given in terms of continuous compounding and look more like
X(0) * Exp(dblI) than X(0) * (1 + dblI). You seem to be using the
amount in the account at the end of the year as if it were in there the
entire year to determine how much interest has accrued. I would
probably spread the deposits out like an annuity using perhaps months
instead of years (divide interest by 12) as a way of getting better
interest values. The present value and future value equations,
especially with an annuity, do this very well. Perhaps some of this
will help.

James A. Fortune