Subtracting Values in a Query

J

John

I have a table that has two fields; odometer reading and
reading date. I want to able input the reading dates
along with the odometer readings. Then I want my query to
calculate the milage difference between each date.



Example:

DATE READING DIFFERENCE

9/1/04 35000

10/1/04 40000 5000

11/1/04 42000 2000



And so on.............



Thanks in Advance - John
 
S

student

Try this

SELECT Table1.mdate, Table1.reading, (select top 1 tab.reading from table1
tab where tab.reading<table1.reading order by tab.reading desc) as prevread,
Table1.reading-prevread as [Difference]
FROM Table1;

This is the result. mdate reading prevread Difference
01-Sep-04 3000


01-Oct-04 3500 3000 500
01-Nov-04 4200 3500 700
 
H

hermie

John said:
I have a table that has two fields; odometer reading and
reading date. I want to able input the reading dates
along with the odometer readings. Then I want my query to
calculate the milage difference between each date.



Example:

DATE READING DIFFERENCE

9/1/04 35000

10/1/04 40000 5000

11/1/04 42000 2000



And so on.............



Thanks in Advance - John
Click to expand...
 
Top