How to subtract previous record from record

  • Thread starter Thread starter Paul Leaver
  • Start date Start date
P

Paul Leaver

Guest
I have a Motorhome which I want to check it's Fuel Economy, seems simple!
I record the odomiter readingwhen I fill up with petrol and the amount of petrol pumped in to fill tank and the cost
I have done it in Excel 97 but I would like to convert to an Access 97 Database.
The purpose is to open an Access Form, add a record with Three Fields, namely the odomiter reading, Amount of Fuel pumped into tank, and Cost.
I would then like Access to subtract the previous record's odomiter reading from the new odomiter reading to obtain the distance traveled since last fill and using this number and the other 2 fields I can calculate my fuel economy
I tried to attached the Excel (97) spreadsheet for your information but it was too large for this forum
The calculations are based on Australian Dollars, LPGas and Litres but it would make no differance if it were Gallons, US Dollars etc.
I would very much appriciate a note to tell me if anyone is able to assist me with this problem.
Thank You
Paul Leaver
Glenview
Queensland Australia
rpleaver@bigpond.net.au
 
To Paul

Thank you for your response
The web site you directed me to was doing just what I wanted to do
I will work on it
 
I wouldn't use either of the recommended methods. The first will fail if you have any gaps in your ID fields. The second is simply inefficient. The following method is simpler and much more efficient.
Code:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

rs.MovePrevious
If rs.BOF = False Then
    'your calc code
Else
    'no previous value
End If
 
Here's a little code routine that looks forward and backward and on the current event displays the three values. To test it in your database, change "PropertyID" to the name of your primary key field.
PHP:
Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim curID As Long
Dim prevID As Long
Dim nextID As Long
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
curID = rs!PropertyID

rs.MovePrevious
If rs.BOF = False Then
    prevID = rs!PropertyID
Else
    prevID = 0
End If
rs.Bookmark = Me.Bookmark
rs.MoveNext
If rs.EOF = False Then
    nextID = rs!PropertyID
    rs.MovePrevious
Else
    nextID = 99999
End If
MsgBox "curr = " & curID & " - prev = " & prevID & " - next = " & nextID

End Sub
 
Thank you Pat

I did try the first option and I got blank fields where there were gaps in my sequential numbering
I can probably live with that
However I have never written a code so I might have a go at it.
It all sounds gobbaldygook to me but it will be fun to learn
Thanks
Paul Leaver
 
I know subqueries aren't the most efficient, but this may be easier to understand, and won't have a problem with missing numbers:

SELECT tblMileages.Mileage, (SELECT Max(Mileage) from tblMileages AS Alias WHERE Alias.Mileage < tblMileages.Mileage) AS PrevMileage, tblMileages.Mileage - PrevMileage AS Difference
FROM tblMileages;

Substitute your table and field names and give it a try.
 
Why not just use the running sum property on a Report, it's easier
 
Bump! I have a set of price data for trade day one and trade day two for a set of time series data. I need to calculate the change for each day. I have tried to write a subquery as above but need some assistance :)

I have a primary key on the table, and have attempted to select the max PK for a given day prior to the PK on the 23rd April 2012.

Code:
SELECT tblPriceCurves.PricePK, tblPriceCurves.GasDate, tblPriceCurves.TradeDate, tblPriceCurves.MiddayPrice,
 
(SELECT MAX(tblPriceCurves.PricePK) AS Alias  FROM tblPriceCurves WHERE Alias.PricePK < tblPriceCurves.PriceFK) AS PreviousRecord
 
FROM tblPriceCurves
WHERE (((tblPriceCurves.TradeDate)=#4/23/2012#));

Price curve on the 23rd April 2012:

ID GasDate Price

1 1/5/12 50
2 2/5/12 51
3 3/5/12 49.5

Price curve on the 24th April 2012:

ID GasDate Price

1 1/5/12 51
2 2/5/12 52
3 3/5/12 50

Output:

GasDate Price

1/5/12 1
2/5/12 1
3/5/12 0.5

Thanks for any help!
 
not checked everything in detail

assuming all the fills have a time stamp, then do a query that sorts fills for the target vehicle with a fill date less than your target date, sorting by descending fill date.

do this as a select 1.

this query will now give the last fill.

----
the trouble with access is that it does not work in the same way as excel. in particular there just is no previous or next row, so you have to work hard to "get at" those rows.

on the other hand things that are hard to do in excel, are trivial in access.

the solution is often to arrange things so you do not need to find a last or previous rows. for instance, if you are checking fuel consumption over a range of dates you can do this

a) select the dates
b) extract the odo reading for the lowest date fill
c) extract the odo reading for the highest fill
d) sum the total fills
e) work out the average

note that you do need to think about opening/closing readings, and whether the fuel "fills" the tank or not. you do not have to work out an opening/closing od for each fuel fill, though, when you do it this way.
 
Hi Dave, principles apply but my problem is slightly different to the OP. I have the below sets of data in tblPriceCurves.

Price curve on the 23rd April 2012:

ID GasDate Price

1 1/5/12 50
2 2/5/12 51
3 3/5/12 49.5

Price curve on the 24th April 2012:

ID GasDate Price

1 1/5/12 51
2 2/5/12 52
3 3/5/12 50

Output (difference between price curves for the first 3 days of May):

GasDate Price

1/5/12 1
2/5/12 1
3/5/12 0.5
 

Users who are viewing this thread

Back
Top Bottom