Continuous performance calculation

[Arvidsson]

Hello,

maybe I can not not see the forest for the trees ... or it is realy difficult.

I have a table like:
-> tblPerformance

P_ID | P_Date  | Performance
------------------------------
1 | 01.01.2010   |     1,03
2 | 02.01.2010   |     1,05
3 | 04.01.2010   |     0,98
4 | 05.01.2010   |     1,00
5 | 07.01.2010   |     0,95

And I want to calculate the overall performance
e.g.:
02.01.2010: 1,03*1,05 = 1,0815
04.01.2010: 1,0815 * 0,98 = 1,0598
05.01.2010: 1,0598 * 1 = 1,0598
07.01.2010: 1,0598 * 0,95 = 1,0068

The result should be:
-> tblPerformanceContinuous

P_ID | P_Date  | Performance | PerformanceContinuous
-----------------------------------------------------------
1 | 01.01.2010   |     1,03 | 1,0300
2 | 02.01.2010   |     1,05 | 1,0815
3 | 04.01.2010   |     0,98 | 1,0598
4 | 05.01.2010   |     1,00 | 1,0598
5 | 07.01.2010   |     0,95 | 1,0068

At first I tried to link (b.tblPerformance.P_ID= a.tblPerformance.P_ID-1) but then I can only multiply two performances.

Does anybody have an idea?

Thank you very much in advance!

 

[vbaInet]

Isn't this related to your other thread?
http://www.access-programmers.co.uk/forums/showthread.php?t=267028&page=1

We asked to see the SQL statement that you said wasn't working but you said you sorted it out.

Did you do both joins?

 

[Arvidsson]

Isn't this related to your other thread?
http://www.access-programmers.co.uk/forums/showthread.php?t=267028&page=1

We asked to see the SQL statement that you said wasn't working but you said you sorted it out.

Did you do both joins?

No, this problem is fixed.
This performance calculation is quite different.

 

[Galaxiom]

Join the table to an alias of itself. (Drag it in to the query designer twice.)

Set criteria to choose the Min of the datefield in the alias that are greater than the datefield in the first copy of the table. This uses a subquery.

Then do the calculation on those rows.

 

[vbaInet]

No, this problem is fixed.
This performance calculation is quite different.

It's a different calculation but the steps to create the needed joins are pretty much the same. You use the same joins as given in the thread (same as what Galaxiom mentioned) and perform your calculations based on the values derived from both joined tables.