ms access - SQL query to calculate the difference between current and previous day's value -

i have access .mdb database table looks similar to:

+---------+------+--------+ |     date        | value | +---------+------+--------+ |2011-05-04 12:00 | 45.9  | |2011-05-05 12:00 | 21.2  | |2011-05-06 12:00 | 32.2  | |2011-05-07 12:00 | 30.4  | |2011-05-08 12:00 | 40.4  | |2011-05-09 12:00 | 19.8  | |2011-05-10 12:00 | 29.7  | +-------+---------+-------+ 

i create query return values derived subtracting 1 value previous day value.

for example: query calculate (21.2-45.9) , return -24.7 (32.2-21.2) , return -11.0 (30.4-32.2) , return -1.8 etc

how can accomplish in select statement?

you can use query employs self-join on table in question:

select    t.datevalue , t.singlevalue - iif(isnull(tnext.singlevalue), 0, tnext.singlevalue)    test t    left join test tnext   on t.datevalue = dateadd("d", -1, tnext.datevalue)    t.datevalue = #2011-05-08 12:00#; 

outputs

datevalue               expr1001 ----------------------  ---------------- 05/08/2011 12:00:00 pm  20.6000022888184 

ddl , inserts below

create table test (datevalue datetime, singlevalue single);  insert test values (#2011-05-04 12:00#, 45.9); insert test values (#2011-05-05 12:00#, 21.2); insert test values (#2011-05-06 12:00#, 32.2); insert test values (#2011-05-07 12:00#, 30.4); insert test values (#2011-05-08 12:00#, 40.4); insert test values (#2011-05-09 12:00#, 19.8); insert test values (#2011-05-10 12:00#, 29.7);