how do i set up a vehicle maintenance database?

[Mr. Maverick]

I'm using excel 2007 and I want to monitor #80 vehicles as far as the oil
change date, the registration renewal date, the daily mileage and fuel usage.
Is there an easier way than trying to create my own table?

 

[Evi]

Tables, please - not Table!

My only knowledge of cars is that some have 4 wheels but I can tell you that
at the least, you will need.

TblMake
MkID (Autonumber, Primary Key)
VMake (eg Ford, Vauxhall)

TblMakeModel
ModID (Autonumber, Primary Key)
MkID (linked from TblMake)
VModel (eg Corsair, Capri)

TblVehicle
VehID (Autonumber, Primary Key)
ModID (linked from TblMakeModel)
RegPlate (I'm not making this the Primary Key field because it can change)
PurchaseDate or whatever car people need to know
Other stuff that only applies to that particular vehicle

TblProcedure
ProID (Autonumber, Primary Key)
VProceed (oil change, Reg renewal, mileage reading - list of all the things
you might want to do to any vehicle)
Frequ (possibly) ie how often this check or procedure needs to be carried
out on any vehicle in days


TblVehicleMaintance Things to do with a particular procedure being carried
out on a particular vehicle.

VehMainID ((Autonumber, Primary Key)
VehID (link from TblVehicle)
ProID (link from TblProcedure
ProDate (the date this check was carried out on that vehicle
Other fields as appropriate
Mileage (mileage reading as a number)
FuelAmt (amount of fuel bought litres)
ProcCost (cost of the procedure
lots of other fields possible.

Other tables may also be needed - the car people here and you yourself can
suggest stuff when you see how things are grouped together - Possibles might
be a parts table, containing stuff like Tyres, Exhaust which may also be
linked to the VehicleMaintnenance table? I'm not sure about that.

From the data in this you can work out - using queries - not the tables, how
many miles to the hour any one vehicle has done, when it is next due to
require a procedure

You can do a Google to see if there is a free or shareware example of the
same thing, otherwise, you're in the right place for starting.your own.

Get that database design right though. It will be well worth doing that
otherwise you are building your house on the sand., And forget all about
putting stuff into a table like you did with Excel.
You will use a nice form with combo boxes and like it!

Evi

 

[Tony Toews [MVP]]

I'm using excel 2007 and I want to monitor #80 vehicles as far as the oil
change date, the registration renewal date, the daily mileage and fuel usage.
Is there an easier way than trying to create my own table?

As Evi indicates you will need more than one table given the daily
mileage and fuel usage. You would likely want to track each
individual oil change date which would be another table as well. You
can download the fleet management system from my website at
http://www.granite.ab.ca/fleet/ and poke about in the demo.mdb file to
see what tables I have.

Note that I do not currently have the daily mileage and fuel usage but
that's coming soon.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John W. Vinson]

On Sat, 22 Mar 2008 14:15:01 -0700, Mr. Maverick <Mr.

I'm using excel 2007 and I want to monitor #80 vehicles as far as the oil
change date, the registration renewal date, the daily mileage and fuel usage.
Is there an easier way than trying to create my own table?

Do you want to do this *in Excel*, which is an excellent spreadsheet program?
or in Access, which is probably more appropriate for this application as it is
a relational database?

They are very different programs, and you'll need to spend some skull sweat
learning about such arcana as "Normalization" and "Query optimization" but I
think you'll find that the effort will be repaid.