Value variation function

[Raul Sousa]

I have a table with two fields; A_Date and A_Value.
I need to create a query for this table showing daily variation.
So, on day 10, for example, I will have the variation from day 9 to day 10.
There must be a way to do this, maybe a function or something.
I just don’t know how to, so any is most welcome.

 

[Golfinray]

Datediff is the command to give you the difference in dates if that is what
you are looking for. Datediff("d",[A_date],[A_value]) should give it to you.

 

[Michel Walsh]

If you have one and only one value per day, and for each day, then:


SELECT a.a_date, a.a_value, a.a_value-b.a_value
FROM yourTableName AS a LEFT JOIN yourTableName AS b
ON a.a_date = b.a_date+1


should do. The idea is to bring the table TWICE, one with an alias 'a', one
with the alias 'b', and we impose that the date under alias 'b' is one day
before the date pointed by alias 'a'.


Hoping it may help,
Vanderghast, Access MVP