Fuel tracking in vehicle database

[noname]

Hello all, I use Access to keep track of approx. 80 vehicles.
I have a table of vehicles with specs.
I have another of service records linked to the vehicle table.
Works great! I can expand each vehicle to see what service I have performed
on just that vehicle.
I also have tables for billing categories, employees and departments.

So far so good.
Now I need to make it to where I can enter the fuel used by each vehicle
when someone fills it up.
I am having trouble figuring out how to total the amounts.
Do I need a table just for fuel entries, then a query to total it or what?

In the end what I would like is to have a form for entering the vehicle#,
amount, date, driver, mileage.
Then have it figure each month the miles driven, fuel used, and mpg.

Any help would be greatly appreciated.

[Ken Snell]

see reply in comp.database.ms-access

--

Ken Snell
<MS ACCESS MVP>

"noname" <no*************@nospam.net> wrote in message
news:li*******************@fe03.lga...

Hello all, I use Access to keep track of approx. 80 vehicles.
I have a table of vehicles with specs.
I have another of service records linked to the vehicle table.
Works great! I can expand each vehicle to see what service I have
performed
on just that vehicle.
I also have tables for billing categories, employees and departments.

So far so good.
Now I need to make it to where I can enter the fuel used by each vehicle
when someone fills it up.
I am having trouble figuring out how to total the amounts.
Do I need a table just for fuel entries, then a query to total it or what?

In the end what I would like is to have a form for entering the vehicle#,
amount, date, driver, mileage.
Then have it figure each month the miles driven, fuel used, and mpg.

Any help would be greatly appreciated.

[Ken Snell]

Yes, use a separate table for your fuel entries. As you note, you'll want
fields in that table that identify the vehicle (will be a foreign key back
to your vehicles table), the driver (probably a foreign key back to your
Drivers table), etc. Each record in the table will be a single refueling of
that vehicle.

Then you'll need to write queries to get the data you wish. You likely will
need to use a subquery if you want to "calculate" the miles driven since the
last refueling, where the subquery would return the mileage number from the
record whose date is the one most previous to the date of the current
(essentially, the Max date where the date is < the date of the current
record).
--

Ken Snell
<MS ACCESS MVP>

"noname" <no*************@nospam.net> wrote in message
news:li*******************@fe03.lga...

Hello all, I use Access to keep track of approx. 80 vehicles.
I have a table of vehicles with specs.
I have another of service records linked to the vehicle table.
Works great! I can expand each vehicle to see what service I have
performed
on just that vehicle.
I also have tables for billing categories, employees and departments.

So far so good.
Now I need to make it to where I can enter the fuel used by each vehicle
when someone fills it up.
I am having trouble figuring out how to total the amounts.
Do I need a table just for fuel entries, then a query to total it or what?

In the end what I would like is to have a form for entering the vehicle#,
amount, date, driver, mileage.
Then have it figure each month the miles driven, fuel used, and mpg.

Any help would be greatly appreciated.