473,856 Members | 1,750 Online

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

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

Nov 13 '05 #1
14 2477
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******@pcdata sheet.com
www.pcdatasheet.com

"Crimsonwin gz" <je**@goldwingz .com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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

Nov 13 '05 #2
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

Nov 13 '05 #3
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******@pcdata sheet.com
www.pcdatasheet.com
"Crimsonwin gz" <je**@goldwingz .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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

Nov 13 '05 #4
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

Nov 13 '05 #5
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
PercentOfSalary Deposit
PercentReturnOn Savings
PercentRaisePer Year

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

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:
AgeForCalculati on:[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******@pcdata sheet.com
www.pcdatasheet.com

"Crimsonwin gz" <je**@goldwingz .com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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

Nov 13 '05 #6
> 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,
curReturnOnSavi ngs As Currency) As Currency

Const intRetirement As Integer = 50
Dim intYear As Integer

CalcRetirement = curSavings

For intYear = intAge To intRetirement
CalcRetirement = (1 + curReturnOnSavi ngs) * 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
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...

Nov 13 '05 #7
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

Nov 13 '05 #8
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....

Nov 13 '05 #9
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!

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.