473,856 Members | 1,750 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
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:
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
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...

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.

Similar topics

8
5805
by: euang | last post by:
Hi, I have been using access 2000 for two years on WINDOWS NT to display dynamic aweb page using ASP My ISP has now changed to Windows 2003, and I am having major problems displaying information from MEMO fields within the Access 2000 database. I have not had any problems before displaying MEMO fields on NT and have tried various tips to resolve this issue but no luck so far. I was wondering if anyone had come accross a similar...
3
3720
by: CSDunn | last post by:
Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what a spreadsheet might show, the column names will actually be dynamic, based on data from a SQL Server 2000 database. The row data will also come from the same database. So in this case, I will have a main report and a subreport. I've already tried...
1
1653
by: Geir Baardsen | last post by:
Hi! I had some thoughts on creating a dll-file in Delphi 7, and have this dll contain some functions to use in my access 2000 db. How do I call the dll-file from within my access 2000 db? Anybody that knows or know of an article on this? Kind regards, Me.Name
2
2038
by: David C. Barber | last post by:
upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code that I've changed to ADO code is working fine, HOWEVER the form Record Source itself does not seem willing to return data. I've set the Record Source to both the query, and the SQL contained within the query, and although the system pauses long enough to have gone out and retrieved the data, I can't see it. The form itself remains gray. In addition: ...
5
2006
by: bobdydd | last post by:
Hi Everbody Access 2000, Windows XP, Outlook 2000, When I open my database it is set to attach to my Outlook 2000 Inbox. So far so good..... The attaching procedure works OK and I am able to see my Outlook 2000 Inbox........Which I can view as a table, within my Access 2000 database.
1
1444
by: Psychobudgie | last post by:
Hi, I have written a stored procedure which includes a DATEPART command, i.e. DATEPART(weekday, <date>) The result when ran from SQL Query Analyser is as expected . i.e. Sunday returns 1, Monday 2, etc When the same proc is called from within the Access 2000 project Sunday
4
2353
by: ThePhenix | last post by:
Hi everybody, I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend. I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified...
4
2921
by: GladGad | last post by:
I am not a real well versed query writer and therefore am having some problems figuring out how to write a parameter query where I want to input a partial name. I have tried a few different things, but either get no results or all of the records in BANKS are returned. I am using Access 2000 on XP. I am using three tables for this query: BANKS BankID (pk) BankName Other fields that are not pertinent to this query VENDORS ...
5
5091
by: HowHow | last post by:
First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still. I have link table called dbo_Roster to access 2000, I try to group data by Service and dates, for example 01/01 to 01/31 is Jan08, 01/02 to 28/2 is Feb08 and so on. The result should be like below : Service Jan08 Feb08 Mar08 Apr08 May08 Jun08 ...
0
9758
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11051
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10773
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9527
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7929
isladogs
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5756
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4571
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 we have to send another system
3
3196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.