Gas consumption Log

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a DB where I keep track of two vehicles. I keep track of
maintenance, Detailing, Modifications, notes, and the newest table in the DB
is GAS CONSUMPTION LOG which I kept in excel and it would figure the MPG for
me based on the values of cells and so on.
I have come to a road block with ACCESS, I would like for the colum MPG in
Table Gas LOG , to be have a formula that would be able to take the values of
previous records and figure out the MPG.
I have the following colums: Gallons Bought Milage MPG
In order to figure the MPG, i have to take the previous milage, substract
the current milage when I pumped gas and then divide this by the amount of
gallons bought.
This can be done easily in excell, but in access I do not know how.

Thank you
 
Keep in mind that the suggested solution requires:

1) Records are ALWAYS entered into the file in proper (date ?)
sequence.

2) Records are never deleted from the middle of the table

3) Records are never deleted from the end of the table.

4) All the information in the table is obviously related to ONE
vehicle.


Ron
 
Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ron2005 said:
Keep in mind that the suggested solution requires:

1) Records are ALWAYS entered into the file in proper (date ?)
sequence.
Click to expand...

There has to be a defined sequence in any relational database.
2) Records are never deleted from the middle of the table
Click to expand...

Why? Since the previous/next value is looked up at the time, it will still
work even if a record is deleted.
3) Records are never deleted from the end of the table.
Click to expand...

Why? We are not relying on having sequential autonumbers, so the subquery or
DLookup() still gives the correct answer when a record is removed. That's
that the normalized structure is about.
4) All the information in the table is obviously related to ONE
vehicle.
Click to expand...

You just need to add another field for the VehicleID if you have a fleet,
and match this field also in the subquery/DLookup().
 
The sample that I was looking at was using the ID number (autonumbered)
and was subtracting 1 and adding 1 to that number.

It would seem that that would cause it to come up with the wrong
number. But then again I have not tried coding using the autonumber in
that way. I was looking at it purely from the point of view of watching
the numbers change not from the point of view of experience.

If Access can handle the skipped number automatically then you are
completely correct and basically all of my comments are irrelevent.

Sorry.

Ron
 
Back
Top