Acumulated Value

[Marcelo Henderson via AccessMonster.com]

I have table "Index":
indexID;
IndexDt
Previous
Today
Accumulated

ID| Date |Previous| Today |Acumulated
1 | 10/08/07 | 0 | 2 | 2
2 | 11/08/07 | 2 | 2 | 4
3 | 12/08/07 | 4 | 3 | 12
4 | 13/08/07 | 12 | 2 | 24
5 | 14/08/07 | 24 | 2 | 48


How can I effect the calculations:
Previous is Yesterday acumulated

 

[John Spencer]

SELECT ID, Date, Today, Acumulated,
(SELECT Acumulated
FROM Index As Y2
WHERE Y2.[Date]-1 = Y1.[Date]) as Previous
FROM Index as Y1

That assumes that you have only one record per date. You could also do
the calculation with INDEXID assuming that the numbers are always
sequential and there are no breaks in the numbering. If there are
breaks in the numbering or in the dates then you can accomplish the task
but the SQL will be more complex.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marcelo Henderson via AccessMonster.com]

No results, Spencer.

SELECT Y1.Date, Y1.ID, NZ((SELECT sum ([Today]) FROM Index AS Y2
WHERE Y2.ID = Y1.ID AND Y2.[Date] < Y1.[Date]),0) AS [Previous],
Y1.Today, IIf([Previous]*[Today]>0,[ Previous]*[Today],[Today]) AS Acumulated,
Y1.Previous
FROM Index AS Y1;

But I get SUM. ""Select sum..." I wanna Multiplication,

Some idea?

Thanks

Regards

Henderson

 

[John Spencer]

What does "No results" mean? No records returned? An Error Message
returned?
Try breaking your query down into parts and see where it breaks down. You
might find it easier to build a base query and then use that query as the
source to get Acumulated.

SELECT Y1.Date, Y1.ID
, (SELECT sum ([Today])
FROM Index AS Y2
WHERE Y2.ID = Y1.ID
AND Y2.[Date] < Y1.[Date]) AS [Previous],
Y1.Today,
FROM Index AS Y1;

Save the above as qIndexBase. Now use that to get your final results
SELECT Q.Date, Q.ID, Q.[Previous],Q.Today,
IIf([Previous]*[Today]>0,[ Previous]*[Today],[Today]) AS Acumulated,
FROM qIndexBase AS Q

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marcelo Henderson via AccessMonster.com]

Error results

At first day, previous = 1 like a neutral factor
if today=2
acumulated= previous * today = 2

second day: previous= acumulated of first day =2
if today-=3
acumuluated= previous*today = 6

third day: previous=acumulated of second day=6
today=2
acumulated=12 . . .


did understand
Regards

Apologyze for my poor english

 

[Marcelo Henderson via AccessMonster.com]

Hello,

I have table "Index":
ID;
IndexDate
Previous
Today
Accumulated


ID IndexDate Previous Today Acumulated
IGPM 01/01/07 1 2 2
IGPM 02/01/07 2 3 6
IGPM 03/01/07 6 2 12
IGPM 04/01/07 12 3 36


Previous = Acumulated of previous date ,if is null = 1

Acumulated= Previous* Today

How can I calculate it?



At first day, previous = 1 like a neutral factor
ex: today=2
acumulated= previous * today = 2

second day: previous= acumulated of first day =2
ex: today-=3
acumuluated= previous*today = 6

third day: previous=acumulated of second day=6
today=2
acumulated=12 . . .


did understand
Regards

 

[Marshall Barton]

No results, Spencer.

SELECT Y1.Date, Y1.ID, NZ((SELECT sum ([Today]) FROM Index AS Y2
WHERE Y2.ID = Y1.ID AND Y2.[Date] < Y1.[Date]),0) AS [Previous],
Y1.Today, IIf([Previous]*[Today]>0,[ Previous]*[Today],[Today]) AS Acumulated,
Y1.Previous
FROM Index AS Y1;

But I get SUM. ""Select sum..." I wanna Multiplication,

Use this kind of thing to get the product:

NZ((SELECT Exp(Sum(Log(Today))) . . . ))

 

[Michel Walsh]

SELECT LAST(a.id), a.date, LAST(a.today), EXP(SUM(LOG(b.today))) As
accumulated
FROM tableName As a INNER JOIN tableName As b
ON a.date >= b.date
GROUP BY a.date


I assume that you have only one record per date.

Today value is never zero (the multiplication will become 0 forever from
that point) neither never negative, neither NULL.



Hoping it may help,
Vanderghast, Access MVP