Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sateeshsql
Starting Member
2 Posts |
 Posted - 2011-08-18 : 07:45:06
|
have created TABLE1 with folloing data.
Trans_date Equip_ID Equip_Name Odometer Liters
01-Aug-11 100 TOYOTA 48400 41
03-Aug-11 100 TOYOTA 48725 30
06-Aug-11 100 TOYOTA 49278 50
10-Aug-11 100 TOYOTA 49454 15
02-Aug-11 200 HILUX 10500 80
09-Aug-11 200 HILUX 10716 40
12-Aug-11 200 HILUX 10954 66
I am trying to add PREVIOUS ODOMETER reading to every transaciton as following.
Trans_date Equip_ID Equip_Name Odometer Liters Prev. Odometer
01-Aug-11 100 TOYOTA 48400 41
03-Aug-11 100 TOYOTA 48725 30 48400
06-Aug-11 100 TOYOTA 49278 50 48725
10-Aug-11 100 TOYOTA 49454 15 49278
02-Aug-11 200 HILUX 10500 80
09-Aug-11 200 HILUX 10716 40 10500
12-Aug-11 200 HILUX 10954 66 10716
I had written qry:
SELECT A.T_date, A.E_Id, A.E_Name, A.Odo, A.Liters, B.Odo
FROM Table1 AS A, Table1 AS B
WHERE B.Odo=(select max(Odo) from Table1 where Odo<A.Odo and E_id=A.E_id);
But i am not getting rows with NO PREVIOUS ODOMETER READING.
pls help. |
|
|
|
|
|
MS Access SQL Query