Well I'm just now getting to work on a brand new database that's goal is to replace 4 different Excel workbooks, all with multiple sheets filled with data.
This will by far be the most complex database I have ever had to create and I already know it's going to be a long daunting task for me with my limited Access knowledge.
This is a fleet tracking database for asset inventory, mileage/hours on equipment (weekly and monthly breakdowns), maintenance schedules and costs, crankcase testing records, and work orders from the field.
I'm trying to wrap my head around what needs to have it's own tables and how the relationships will be setup. The biggest part of this project will be the mileage and maintenance tracking.
Here's what I am thinking for tables / fields:
Assets (asset #, vehicle type [truck or heavy equipment], vehicle year, make & model, VIN or S/N, Tag #, vehicle specific repair info [oil filter number, tire sizes, etc.], current driver/operator)
Mileage/Hours (asset #, weekly mileage or hours [depending on equipment], monthly usage [based off of weekly mileage], monthly iron days [hours per month divided by 8]
Maintenance (asset #, PM #, PM interval @ given mileage/hours [PM1,2,1,3,1,2,1,4], PM Due [based off of current mileage/hours])
PM Schedule (asset #, PM #, oil + SOS, filter, diffs, inner/out af, etc.)
SOS[Scheduled Oil Sampling] (asset #, taken from [engine, front/rear diff, hidraulics, radiator, etc.], sample date, mileage/hours, result [ok, monitor, action], action taken)
Work Orders (asset#, WO#, status, opened by, date written, date received, description of problem, priority, response due date, date responded, action taken, date closed)
And I think that's about the majority of the information that will be in this database. The Maintenance/PM Schedule tables I think are what's going to be hardest to set up but might be easier once I start building and seeing how things come together visually.
Do the above tables make sense? Should any of the tables be combined?
Relationships..... Oh lovely relationships.... So this is what I see:
Assets one-to-many Mileage/Hours, Maintenance, SOS & Work Orders
Maintenance ??? Mileage/Hours, PM Schedule
SOS ??? Mileage/Hours
I'm really confused on how all of these tables have to be linked relationship wise and I would love any and all input on how to set this up.
Thanks in advance, I know there was a lot to read and look through!