Calculate change to prior record.

[Guest]

I have a table with a Pkey called Date which is type text and in yyyymmdd
format. I am doing it this way becasue if had so much trouble with date
formats considering that the standard in my country is ddmmyyyy. The second
field is a Value of type double.

I want to calculate the %change of Value to the prior record.

if the sample data was the following;

Date Value
20040804 25
20040803 23
20040802 26

The %change would calculate (25-23)/23 for the record on 20040804.

Any suggestions?

Bruce

 

[Michel Walsh]

Hi,


A FORMAT is what is displayed. I assume your date, in the table, is a
DATETIME data type, NOT A STRING.



SELECT a.ItemID, a.DateTime, 1-FIRST(b.Value) / iif( 0=FIRST(a.Value), NULL,
FIRST(a.Value))

FROM ( myTable As a INNER JONI myTable As b
ON a.ItemID = b.ItemID AND a.dateTime>b.dateTime )
INNER JOIN myTable As c ON a.ItemID=c.ItemID and a.dateTime>c.dateTime

GROUP BY a.ItemID, a.DateTime, b.DateTime
HAVING b.DateTime = MAX( c.DateTIme)



Hoping it may help,
Vanderghast, Access MVP