HELP!! Using calculated value from a previous calculated record andso on - Pulling my hair out!

[wboyd36]

Everyone,

OK, here is what I am trying to do (If I can explain it properly); I
need an expression to calculate a total number with fields
"TotalGainLoss" + "GainLoss". Then take that "Total" number and
replace "TotalGainLoss" in the next row.

Here is an example
"Client_name" "TotalGainLoss"
"GainLoss" "Total"
xxxxxxxxxxxxxxx, Irrevocable Trust 15797.48
-3696.72 12100.76
xxxxxxxxxxxxxxx, Irrevocable Trust 12100.76
-5515 6585.76
xxxxxxxxxxxxxxx, Irrevocable Trust 6585.76 27898.24
34484
xxxxxxxxxxxxxxx, Irrevocable Trust 34484 43695.72
78179.72

It seems pretty easy in Excel....but I need it in MS Access. Should
this be done using a query or a function, or a combo of both?

Thanks,
Wayne Boyd

 

[KARL DEWEY]

Seeing as how records in an Access table are stored like a barrel of bricks
you need a field to determine what the NEXT record is when you sort them.
Do you have such a field?

 

[wboyd36]

Is this a one-time need on an existing dataset, or something that happensas
users input data?

This happens after a data import occurs. User gets data from a
portfolio management system and imports it into the database -
"tblTaxGainLoss". No user input besides the import is needed. this
data gives the portfolio manager a sense of what stocks to sell and
what to keep.
Here is what the data looks like after import:
(Data is sorted by Client_number)
ID Clien_number Client_name TotalGainLoss stock quantity date
unit_cost price GainLoss Value Percent Sold
1 6303s Christina Stansell, Irrevocable Trust 15797.48 wfmi 100 27-
Apr-07 47.5272 10.56 -3696.72 1056 -0.7778114427107
2 6303s Christina Stansell, Irrevocable Trust 15797.48 mer 100 30-
Jan-06 74.64 19.49 -5515 1949 -0.738879957127546
3 6303s Christina Stansell, Irrevocable Trust 15797.48 c 100 15-Nov-04
46.7 14.21 -3249 1421 -0.695717344753747
4 6303s Christina Stansell, Irrevocable Trust 15797.48 ebay 100 27-
Apr-07 34.39 15.5471 -1884.29 1554.71 -0.547917999418436
5 6303s Christina Stansell, Irrevocable Trust 15797.48 fo 100 23-
Nov-05 79.87 39.49 -4038 3949 -0.505571553774884
6 6303s Christina Stansell, Irrevocable Trust 15797.48 wy 100 24-
Jun-05 65.25 35.35 -2990 3535 -0.45823754789272
7 6303s Christina Stansell, Irrevocable Trust 15797.48 ebay 200 27-
Sep-06 27.2182 15.5471 -2334.22 3109.42 -0.428797642753746
8 6303s Christina Stansell, Irrevocable Trust 15797.48 wmb 200 16-
Jul-08 34.51 20.44 -2814 4088 -0.407707910750507
9 6303s Christina Stansell, Irrevocable Trust 15797.48 xlf 400 07-
May-08 27.14 16.24 -4360 6496 -0.401621223286662
10 6303s Christina Stansell, Irrevocable Trust 15797.48 adbe 200 27-
Apr-07 42.77 28.63 -2828 5726 -0.330605564648118
11 6303s Christina Stansell, Irrevocable Trust 15797.48 spy 240 14-
Jan-08 140.95 99.46 -9957.6 23870.4 -0.294359702021994
12 6303s Christina Stansell, Irrevocable Trust 15797.48 xlnx 400 08-
Jan-07 24.318 17.82 -2599.2 7128 -0.267209474463361
13 6303s Christina Stansell, Irrevocable Trust 15797.48 intc 100 16-
Jul-08 20.888 15.84 -504.8 1584 -0.241669858291842
14 6303s Christina Stansell, Irrevocable Trust 15797.48 wye 200 02-
Feb-05 39.32 34.17 -1030 6834 -0.130976602238047
15 6303s Christina Stansell, Irrevocable Trust 15797.48 jnj 100 24-
Apr-02 64.56 61.5 -306 6150 -4.73977695167287E-02
16 6303s Christina Stansell, Irrevocable Trust 15797.48 d 200 10-
Aug-05 37.845 36.2 -328.999999999999 7240 -4.34667723609459E-02
17 6303s Christina Stansell, Irrevocable Trust 15797.48 nqlr -1 22-
Oct-08 0.2599 0.38 -0.1201 -0.38 0.462100808003078
18 6303s Christina Stansell, Irrevocable Trust 15797.48 yaakk -1 17-
Oct-08 1.7299 5.5 -3.7701 -5.5 2.17937453031967

The TotalGainLoss Column represents the year-to-date G\L figures for
the account and the "GainLoss" column represents the G\L for the
position (stock/option) held in the account.

The Portfolio manager takes the original "TotalGainLoss" number and
adds it to the "GainLoss" and comes up with a total. That total is
then moved to the next row of the "TotalGainLoss" and then added to
the "GainLoss" in the current row....and so on.

In the first example posted....the original "TotalGainloss" is
15797.48 and it is added to the "GainLoss" of -3696.72 to get a total
of 12100.76.
In the next row the "TotalGaonLoss" should be replaced with the total
from the previous row (12100.76) and it should be added to the
"GainLoss" to get another total.....and so on....

 

[wboyd36]

Then I think where KARL was headed was to use the ID field to pull in an
editable recordset sorted on the ID field, loop through them, and perform
your update that way.  That's what I would do.

But he may have a better idea.

Do you have experience with recordsets and loops in VBA?

Is this a one-time need on an existing dataset, or something that happens as
users input data?

[quoted text clipped - 29 lines]

- Show quoted text -

This happens after a data import occurs.  User gets data from a
portfolio management system and imports it into the database -
"tblTaxGainLoss".  No user input besides the import is needed.  this
data gives the portfolio manager a sense of what stocks to sell and
what to keep.
Here is what the data looks like after import:
(Data is sorted by Client_number)
ID  Clien_number    Client_name     TotalGainLoss   stock   quantity        date
unit_cost   price   GainLoss        Value   Percent Sold
1   6303s   Christina Stansell, Irrevocable Trust   15797.48        wfmi    100     27-
Apr-07      47.5272 10.56   -3696.72        1056    -0.7778114427107
2   6303s   Christina Stansell, Irrevocable Trust   15797.48        mer     100     30-
Jan-06      74.64   19.49   -5515   1949    -0.738879957127546
3   6303s   Christina Stansell, Irrevocable Trust   15797.48        c       100     15-Nov-04
46.7        14.21   -3249   1421    -0.695717344753747
4   6303s   Christina Stansell, Irrevocable Trust   15797.48        ebay    100     27-
Apr-07      34.39   15.5471 -1884.29        1554.71 -0.547917999418436
5   6303s   Christina Stansell, Irrevocable Trust   15797.48        fo      100     23-
Nov-05      79.87   39.49   -4038   3949    -0.505571553774884
6   6303s   Christina Stansell, Irrevocable Trust   15797.48        wy      100     24-
Jun-05      65.25   35.35   -2990   3535    -0.45823754789272
7   6303s   Christina Stansell, Irrevocable Trust   15797.48        ebay    200     27-
Sep-06      27.2182 15.5471 -2334.22        3109.42 -0.428797642753746
8   6303s   Christina Stansell, Irrevocable Trust   15797.48        wmb     200     16-
Jul-08      34.51   20.44   -2814   4088    -0.407707910750507
9   6303s   Christina Stansell, Irrevocable Trust   15797.48        xlf     400     07-
May-08      27.14   16.24   -4360   6496    -0.401621223286662
10  6303s   Christina Stansell, Irrevocable Trust   15797.48        adbe    200     27-
Apr-07      42.77   28.63   -2828   5726    -0.330605564648118
11  6303s   Christina Stansell, Irrevocable Trust   15797.48        spy     240     14-
Jan-08      140.95  99.46   -9957.6 23870.4 -0.294359702021994
12  6303s   Christina Stansell, Irrevocable Trust   15797.48        xlnx    400     08-
Jan-07      24.318  17.82   -2599.2 7128    -0.267209474463361
13  6303s   Christina Stansell, Irrevocable Trust   15797.48        intc    100     16-
Jul-08      20.888  15.84   -504.8  1584    -0.241669858291842
14  6303s   Christina Stansell, Irrevocable Trust   15797.48        wye     200     02-
Feb-05      39.32   34.17   -1030   6834    -0.130976602238047
15  6303s   Christina Stansell, Irrevocable Trust   15797.48        jnj     100     24-
Apr-02      64.56   61.5    -306    6150    -4.73977695167287E-02
16  6303s   Christina Stansell, Irrevocable Trust   15797.48        d       200     10-
Aug-05      37.845  36.2    -328.999999999999       7240    -4.34667723609459E-02
17  6303s   Christina Stansell, Irrevocable Trust   15797.48        nqlr    -1      22-
Oct-08      0.2599  0.38    -0.1201 -0.38   0.462100808003078
18  6303s   Christina Stansell, Irrevocable Trust   15797.48        yaakk   -1      17-
Oct-08      1.7299  5.5     -3.7701 -5.5    2.17937453031967

The TotalGainLoss Column represents the year-to-date G\L figures for
the account and the "GainLoss" column represents the G\L for the
position (stock/option) held in the account.

The Portfolio manager takes the original "TotalGainLoss" number and
adds it to the "GainLoss" and comes up with a total.  That total is
then moved to the next row of the "TotalGainLoss" and then added to
the "GainLoss" in the current row....and so on.

In the first example posted....the original "TotalGainloss" is
15797.48 and it is added to the "GainLoss" of -3696.72 to get a total
of  12100.76.
In the next row the "TotalGaonLoss" should be replaced with the total
from the previous row (12100.76) and it should be added to the
"GainLoss" to get another total.....and so on....

Not a lot...but I can probably thumb my way through it....

I was considering going in this direction.....

..do until EOF
"SQL" Statement
results
..next

Can't quite figure this one out....

 

[wboyd36]

Check here:

http://msdn.microsoft.com/en-us/library/aa172265(office.11).aspx

Use whichever method with which you are most comfortable.

Then I think where KARL was headed was to use the ID field to pull in an
editable recordset sorted on the ID field, loop through them, and perform

[quoted text clipped - 77 lines]

- Show quoted text -

Not a lot...but I can probably thumb my way through it....

I was considering going in this direction.....

.do until EOF
"SQL" Statement
results
.next

Can't quite figure this one out....

I understand the the recordset part, etc....it's the logic that I
can't figure out....How do I make the records do what I want them to.

 

[wboyd36]

This is very do-able, I just haven't had time.

Questions:

1) Is this iteration of calculations to start with one client, then start
over when the client changes?

2) What formula (just in laymans terms, or Excel terminology) would you use
to fill in the 'TotalGainLoss' for the first record?  If that's the value
you're looking to calculate, it also refers to itself, so how do you get
started in record one?

3) Once you have a 'TotalGainLoss' value in the first row, what formula would
you use to calculate the second, and subsequent rows?

[quoted text clipped - 24 lines]

- Show quoted text -

I understand the the recordset part, etc....it's the logic that I
can't figure out....How do I make the records do what I want them to.

OK...I hope this helps....

1) Is this iteration of calculations to start with one client, then
start
over when the client changes?

Yes, each client is evaluated separately

2) What formula (just in laymans terms, or Excel terminology) would
you use
to fill in the 'TotalGainLoss' for the first record? If that's the
value
you're looking to calculate, it also refers to itself, so how do you
get
started in record one?

TotalGainLoss is provided outside of this formula. It happens to be
the cumulative realized gain or loss to date for each account. It is
the starting point for these calculations. It is probably reference
only once per client and represents the starting point only.

The ‘TotalGainLoss’ in the first record is imported from the portfolio
management system.

3) Once you have a 'TotalGainLoss' value in the first row, what
formula would
you use to calculate the second, and subsequent rows?

The formula is simple addition….example

‘TotalGainLoss’ ‘GainLoss’ ‘Total’
15797.48 -3696.72 12100.76

The ‘Total’ from the previous row is brought down to the
‘TotalGainLoss’ column in the next row – and so on until EOF.
‘TotalGainLoss’ ‘GainLoss’ ‘Total’
12100.76 - 5515 6585.76

Next row
‘TotalGainLoss’ ‘GainLoss’ ‘Total’
6585.76 27898.24 34484

Data imported from the portfolio management system includes
‘TotalGainLoss’ and ‘GainLoss’ (along with ‘client_name’, number,
etc.). The ‘Total’ column is the result of ‘TotalGainLoss’ +
‘GainLoss’.

 

[wboyd36]

That helps.  I may not get a chance to work on it until the weekend though.

[quoted text clipped - 23 lines]OK...I hope this helps....

1) Is this iteration of calculations to start with one client, then
start
over when the client changes?

Yes, each client is evaluated separately

2) What formula (just in laymans terms, or Excel terminology) would
you use
to fill in the 'TotalGainLoss' for the first record?  If that's the
value
you're looking to calculate, it also refers to itself, so how do you
get
started in record one?

TotalGainLoss is provided outside of this formula. It happens to be
the cumulative realized gain or loss to date for each account. It is
the starting point for these calculations. It is probably reference
only once per client and represents the starting point only.

The ‘TotalGainLoss’ in the first record is imported from the portfolio
management system.

3) Once you have a 'TotalGainLoss' value in the first row, what
formula would
you use to calculate the second, and subsequent rows?

The formula is simple addition….example

‘TotalGainLoss’ ‘GainLoss’       ‘Total’
15797.48           -3696.72         12100.76

The ‘Total’ from the previous row is brought down to the
‘TotalGainLoss’ column in the next row – and so on until EOF.
‘TotalGainLoss’ ‘GainLoss’        ‘Total’
 12100.76  -       5515                  6585.76

Next row
‘TotalGainLoss’ ‘GainLoss’            ‘Total’
6585.76             27898.24          34484

Data imported from the portfolio management system includes
‘TotalGainLoss’ and ‘GainLoss’ (along with ‘client_name’, number,
etc.). The ‘Total’ column is the result of ‘TotalGainLoss’ +
‘GainLoss’.

Thanks so much.

 

[wboyd36]

That helps.  I may not get a chance to work on it until the weekend though.

This is very do-able, I just haven't had time.
[quoted text clipped - 23 lines]
--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200811/1
OK...I hope this helps....
1) Is this iteration of calculations to start with one client, then
start
over when the client changes?
Yes, each client is evaluated separately
2) What formula (just in laymans terms, or Excel terminology) would
you use
to fill in the 'TotalGainLoss' for the first record?  If that's the
value
you're looking to calculate, it also refers to itself, so how do you
get
started in record one?
TotalGainLoss is provided outside of this formula. It happens to be
the cumulative realized gain or loss to date for each account. It is
the starting point for these calculations. It is probably reference
only once per client and represents the starting point only.
The ‘TotalGainLoss’ in the first record is imported from the portfolio
management system.
3) Once you have a 'TotalGainLoss' value in the first row, what
formula would
you use to calculate the second, and subsequent rows?
The formula is simple addition….example
‘TotalGainLoss’ ‘GainLoss’       ‘Total’
15797.48           -3696.72         12100.76
The ‘Total’ from the previous row is brought down to the
‘TotalGainLoss’ column in the next row – and so on until EOF.
‘TotalGainLoss’ ‘GainLoss’        ‘Total’
 12100.76  -       5515                  6585.76
Next row
‘TotalGainLoss’ ‘GainLoss’            ‘Total’
6585.76             27898.24          34484
Data imported from the portfolio management system includes
‘TotalGainLoss’ and ‘GainLoss’ (along with ‘client_name’, number,
etc.). The ‘Total’ column is the result of ‘TotalGainLoss’ +
‘GainLoss’.

- Show quoted text -

Thanks so much.- Hide quoted text -

- Show quoted text -

Any word?