Use form to update previous record

[smeyer56]

I am playing with a DB to track gas mileage by vehicle, station, E85 vs regular etc.

I am having a problem grasping something. I entering data in a form for each fill-up including price/gallon, gallons bought, miles travelled station bought at etc.

The problem I have is that the gas I buy today will not have results until next purchase for miles travelled and gas used.

Is there a way in Access 2000 to have data entered on a form automatically update certain information in the previous record? This way I can enter all the information for today's purchase but the mileage and gallons used will update for the previous purchase.

 

[MajP]

You do not design your tables that way, but use the power of relational databases. Just capture the data for each fill up, then you are going to use queries to join records to the next date.

1) Here is my table to do this

ID	vehicleID	decMiles	decGallons	dtmDateFilled	currPrice
1	1	100	15	1/2/2008	$3.00
2	1	200	5	1/8/2008	$3.15
3	1	350	7	1/12/2008	$3.05
4	1	550	10	1/16/2008	$3.11
5	2	1000	15	1/3/2008	$3.00
6	2	1200	20	1/11/2008	$3.00
7	2	1280	3	1/15/2008	$3.00

I have 2 different cars that I am tracking (vehicleID 1 and 2)

2) If you are really good at queries which I am not, you could do all of this in one shot. I am only smart enough to build a query that returns the next higher date for a specific vehicle. I call this "qryNextFillUp"

Here is the SQL

SELECT A.vehicleID, A.decMiles, A.decGallons, A.currPrice, A.dtmDateFilled, (select min(dtmDateFilled) from tblGasUseage as B where A.dtmDateFilled < b.dtmDateFilled and A.vehicleID = B.vehicleID) AS nextFillDate
FROM tblGasUseage AS A;

Here is the data

vehicleID	decMiles	decGallons	currPrice	dtmDateFilled	nextFillDate
1	100	15	$3.00	1/2/2008	1/8/2008
1	200	5	$3.15	1/8/2008	1/12/2008
1	350	7	$3.05	1/12/2008	1/16/2008
1	550	10	$3.11	1/16/2008	
2	1000	15	$3.00	1/3/2008	1/11/2008
2	1200	20	$3.00	1/11/2008	1/15/2008
2	1280	3	$3.00	1/15/2008

Now I can use this to join to the original table by nextFillDate to put the current and previous record data together and do my calculations. I call this qryMPG. This looks hard, but you can use the design window for doing it.

SELECT Previous.vehicleID, Previous.decMiles, Previous.dtmDateFilled, Current.dtmDateFilled, Current.decMiles, Current.decGallons, Current.decMiles-previous.decMiles AS MilesDriven, (Current.decMiles-previous.decMiles)/current.decGallons AS MPG
FROM qryNextFillUp AS Previous INNER JOIN tblGasUseage AS [Current] ON (Previous.vehicleID = Current.vehicleID) AND (Previous.nextFillDate = Current.dtmDateFilled)
ORDER BY Previous.vehicleID, Previous.dtmDateFilled, Current.dtmDateFilled;

And the data then looks like

vehicleID	Previous.decMiles	Previous.dtmDateFilled	Current.dtmDateFilled	Current.decMiles	decGallons	MilesDriven	MPG
1	100	1/2/2008	1/8/2008	200	5	100	20.00
1	200	1/8/2008	1/12/2008	350	7	150	21.43
1	350	1/12/2008	1/16/2008	550	10	200	20.00
2	1000	1/3/2008	1/11/2008	1200	20	200	10.00
2	1200	1/11/2008	1/15/2008	1280	3	80	26.67

You can then see that I have returned miles driven between fill ups, and the MPG between fill ups. With a little more work I can calculate cost per mile (is better gas worth it?) etc.

SELECT Previous.vehicleID, Previous.decMiles, Previous.dtmDateFilled, Current.dtmDateFilled, Current.decMiles, Current.decGallons, Current.decMiles-previous.decMiles AS MilesDriven, (Current.decMiles-previous.decMiles)/current.decGallons AS MPG, ([previous].[currPrice]*100)/(([Current].[decMiles]-[previous].[decMiles])/[current].[decGallons]) AS CostPerMile, Previous.currPrice
FROM qryNextFillUp AS Previous INNER JOIN tblGasUseage AS [Current] ON (Previous.vehicleID = Current.vehicleID) AND (Previous.nextFillDate = Current.dtmDateFilled)
ORDER BY Previous.vehicleID, Previous.dtmDateFilled, Current.dtmDateFilled;

data

MPG	CostPerMile(cents)
20.00	15
21.43	14.7
20.00	15.25
10.00	30
26.67	11.25