Record Difference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,

I am trying to determine how best to calulate the variance between two
records. Any help would be greatly appreciated.

Price
------
2.53
2.62
2.59
2.61
2.57

I want to take the second record and divide it by the first resulting in
percentage change. So ((2.62/2.53) - 1) = .03557. But I want to do this for
each row in the table. So I will have a new column in my result set that
looks like:

Price_Delta
 
Hi,


If you have a dateStamp field that uniquely defines the record ordering:


SELECT a.DateStamp, LAST(a.price), LAST( b.price/a.price) -1 as Variation

FROM ( myTable As a INNER JOIN myTable As b
ON a.DateStamp < b.DateStamp) INNER JOIN myTable As c
ON a.DateStamp < c.DateStamp

GROUP BY a.DateStamp, b.DateStamp
HAVING b.DateStamp = MIN(c.DateStamp)


We use 3 references to your table. References b and c are similar, for a
given a.DateStamp, they refer to records with a date in the future. But
while we group by each possible date in reference, b, we only keep the one
with the smallest date. In short, that makes the reference b equal to the
"nearest date following" the one mentioned in the reference a. Which is what
we need: a and b refer then to "successive" records, a occurring before b.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top