Value from previous record in next record

[markfmcc]

I am setting up a database to track my fuel useage versus miles driven.

I have a fuel transactions table that records the transaction date, vehicle number, odometer reading, and gallons pumped.

eg
TrxDate VehNo Odometer Gallons Pumped
1/11/05 222 15000 5
2/11/05 222 15060 10
3/11/05 222 15090 10

I need to crate a report that gives me miles per gallon for each transaction.

In order to get this I need to be able to subtrace the previous records odometer reading from the current records odometer reading. This will give me the miles travelled between readings.

My question is: How do I get the system to pick up the previous reading for a specific vehicl from the fuel transactions table?

Please help.
Thanks in advance,
Mark

 

[Remou]

Hi
Perhaps:

strSQL = "Select * From tblVehicles Where VehNo = " & Me.txtVehNo & " Order By trxDate"

Set rs=CurrentDB.OpenRecordset(strSQL)
rs.FindFirst "ID = " & Me.txtID
If Not rs.Bof Then
   rs.MovePrevious
   Me.[txtLastGallons] = rs![Gallons Pumped]
   Me.[txtOdometer] = rs![Odometer]
Else
   Me.[txtLastGallons] = 0
   Me.[txtOdometer] = 0
End If

I hope that's right, I haven't tested.

 

[dhookom]

I would probably calculate this column in the query. Try SQL like:

SELECT *, 
(SELECT Max(Odometer)
 FROM tblFuelTrans f
 WHERE f.Odometer < tblFuelTrans.Odometer AND
   f.VehNo = tblFuelTrans.VehNo) As PrevOdom
FROM tblFuelTrans;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]