MS Access SQL Query - SQL Server Forums

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.

All Forums

Other Forums

MS Access

MS Access SQL Query

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources