In access 2002 how would divided a field by the next record

[DogLover2006]

I can't figure out how to divided a the field's first row by the field's second row and so on. For example: the field name is Millions in the first record it has 100,580. and in the next record it has 500.00 so now I have to divided it form row one by row two. This need to be done until there are no more records. How can I go about this? I have been stuck on for weeks. Please help.

 

[lespaul]

what determines 'the next record'? Is there a field where the records can be ordered to get 'the next record'?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[DogLover2006]

yes there is an AllFundID. I did think it was going to be this hard to do. I though a loop and a if statement would do.

 

[lespaul]

can you post some sample data that you want to divide and the expected results from that sample dataset?

If you want to dive into VBA you can write a loop that goes through your dataset, collects the AllFundID and looks for the "next" AllFundID, but depending on the size of your dataset, that could take a LONG time to process.

Leslie

 

[DogLover2006]

here is some data: All_Funds
ID year Millions
1 2005-06
2 2004-05
3 2003-04
4 2002-03
5 2001-02 84598
6 2000-01 79753
7 1999-00 73266
8 1998-99 70697
9 1997-98 66156
10 1996-97 62951

The following is the result of (line 3/ line 4)-1 and format as a percentage:

ID Percentage
1
2
3
4 -100.00%
5 6.08%
6 8.85%
7 3.63%
8 6.86%
9 5.09%
10 -0.44%

 

[Crowley16]

your best bet would be to do some fancy joining

something like:

select Fld1, Fld2, Fld1/Fld2
from T1 INNER JOIN T2 ON T1.ID = T2.ID - 1

it would be a good idea to parse out any nulls or 0s as division by them would give a error

If access complains about the join syntax, then you can create a query to process the id's and join the table to the query...

--------------------
Procrastinate Now!

 

[DogLover2006]

How is join them going to divid line one by line two.
Here is an excel exapmle of the formula (b1/b2)-1.

This originaly was in excel but the people now want it in access. So I am trying to figure out how to create a field that give the rusult that excel formula did.

 

[PHV]

How is join them going to divid line one by line two
A starting point (SQL code):
SELECT A.ID, A.year, A.Millions, IIf(Nz(B.Millions,0)<>0,(A.Millions/B.Millions)-1,0) AS Percentage
FROM All_Funds AS A LEFT JOIN All_Funds AS B ON A.ID = (B.ID-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsteph]

Doglover,
I wouldn't try to do it in SQL or by ID, since that may not necesarily be sequential.

Quick & Dirty--Add the field to the table,say, "PctIncrease" or whatever. Then just open a recordset sorted by the year or ID or whatever is a consistent accurate sort field for the Before/After records.
Then just loop through it, saving the previous amount in a variable, then to the calc on the next record--after that calc change the variable before you do the .MoveNext.

Be sure to check for Div/0
--Jim

 

[DogLover2006]

Jim,

I don't know how to write the loop to save the previous amount. I fall short sometime with loops. as far as writing them.

 

[PHV]

Have you tried the query I've suggested you 16 May 06 15:19 ?

 

[DogLover2006]

PHV,

this SELECT A.ID, A.year, A.Millions, IIf(Nz(B.Millions,0)<>0,(A.Millions/B.Millions)-1,0) AS Percentage
FROM All_Funds AS A LEFT JOIN All_Funds AS B ON A.ID = (B.ID-1)
give me 400 records and the results all diffent. I start off with only 19 records and now there are 400. This did not work
thank you for trying

 

[PHV]

What happens if you replace the LEFT JOIN with an INNER JOIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DogLover2006]

PHV,
with the inner join the result ended up 0 all the way down the field.

 

[lespaul]

are you sure all the numbers in All_Funds are sequential? No missing numbers?

 

[PHV]

Could you please post your actual SQL code, actual input values, actual result and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DogLover2006]

PHV,
Here is the actual input:

AllFundID SFY Millions
1 2005-06
2 2004-05
3 2003-04
4 2002-03
5 2001-02 84598
6 2000-01 79753
7 1999-00 73266
8 1998-99 70697
9 1997-98 66156
10 1996-97 62951
11 1995-96 63229
12 1994-95 61899
13 1993-94 57907
14 1992-93 54796
15 1991-92 52328
16 1990-91 48909
17 1989-90 46362
18 1988-89 43450
19 1987-88 39895


My SQL code:

SELECT A.AllFundID, A.[All Funds Closing Balance], A.Millions, IIf(Nz(B.Millions,0)<>0,(A.Millions/B.Millions)-1,0) AS Percentage
FROM All_Funds AS A INNER JOIN All_Funds AS B ON A.AllFundID = B.AllFundID;


My Result:

AllFundID SFY Millions Percentage
1 2005-06 0
2 2004-05 0
3 2003-04 0
4 2002-03 0
5 2001-02 84598 0
6 2000-01 79753 0
7 1999-00 73266 0
8 1998-99 70697 0
9 1997-98 66156 0
10 1996-97 62951 0
11 1995-96 63229 0
12 1994-95 61899 0
13 1993-94 57907 0
14 1992-93 54796 0
15 1991-92 52328 0
16 1990-91 48909 0
17 1989-90 46362 0
18 1988-89 43450 0
19 1987-88 39895 0

 

[PHV]

Replace this:
ON A.AllFundID = B.AllFundID
with this (as I've suggested you):
ON A.AllFundID = B.AllFundID - 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DogLover2006]

PHV,

Thank you that did the trick.

DogLover2006