Calculations in access queries

[Rolandas]

Hello everybody,

I am calculating NAV (net asset value) of portfolio with access and I am interested if it is possible to do some calculations. Here is example:

In one column I have the value of portfolio, e.g.

Value
99999
88888
77777
88888
66666
99999

I need the other column with would be like this:
Value| Unit Value
99999| 1000
88888| 888
77777| 777
88888| 888
66666| 666
99999| 999

So, first value IS ALWAYS 1000. The second value of the column depends on the change of the first column, e.g.
(88888-99999)/99999 = -11,1 %, so
1000 - 11,1 % = 888 %

Is it possible to do that?

Thank You for answers.

[Stewart Ross]

Relational databases do not work like Excel; relational tables have no inherent concept of position, which is a logical imposition on the set of data stored in the table dependent on the combination of fields you may choose to sort the underlying set. The set of data has not actually changed by sorting the rows in a different order, yet sorting in a different order would invalidate what you are trying to achieve.

Excel uses explicit row referencing of the row previous from the current row to achieve the incremental calculations you are suggesting. There is no simple analogue to this in a relational database.

In essence you will need to join the table back to itself using a ranking structure of some kind, or by date, such that the previous record is joined to the current to allow this kind of calculation. It is neither simple nor straightforward to achieve.

You would need to provide us with fuller details of your table structure, and in particular the key fields which would allow for a ranked approach to be taken in SQL.

-Stewart