Differences between rows in a table.

[Kevin]

I have some investments for which I get a periodic
valuation statement. I am trying to write a query that
will calculate how much the valuation has changed each
time I update a table with a new valuation.
Thus the table may show
Date Value
1 Jan 100
15 Feb 125
31 Mar 103
25 Apr 87
How can I calculate the change of 25 for 15 Feb, -22 for
31 Mar, etc.
In addition I would like to be able to determine the
change as a percentage of the opening value, and as
calculate the number of days between the valuations.
Any help much appreciated.
Thanks in advance
Kevin

 

[Michel Walsh]

Hi,


SELECT a.ProductID,
a.theDate,
LAST(a.Value) As ActualNow,
b.theDate As PreviousDate,
LAST(b.Value) AS PreviousValue

FROM ( myTable As a INNER JOIN myTable As b
ON a.ProductID=b.ProductID
) INNER JOIN myTable As c
ON (c.Product.ID=a.ProductID)
AND
(c.TheDate < a.TheDate)

GROUP BY a.ProductID, a.TheDate, b.TheDate

HAVING b.TheDate = MAX(c.TheDate)



You can subtract PreviousValue from ActualValue to get the "change", and
divide by whatever of these two, as long as it not zero, to get a
percentage of change.



Since, for a given ProductID, every date referred by 'c' is less than the
one under consideration for 'a', and since, from 'b' , we only keep those
having the date = max( those from c), b.theDate is clearly the previous
date of a.theDate.

The aggregate LAST(tableName.FieldName) can be read as "take a value of the
supplied field name that belong the actual group defined by tableName). If
you have multiple values to take, LAST insure you that all those would be
picked from the same record. LAST does NOT mean latest, neither lastly
entered record in the table.



Hoping it may help,
Vanderghast, Access MVP