Please help me set up my tables for new database

[10 Gauge]

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!

[jzwp11]

The tables still need more work. This is how I see it based on your description.

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)

I would have a table for vehicle types since you may have many vehicles/assets of the same type

tblAssetTypes
-pkAssetTypeID primary key, autonumber
-txtAssetTypeName


I assume that you also have assets of the same make and model

tblMakes
-pkMakeID primary key, autonumber
-txtMakeName

tblModels
-pkModelID primary key,autonumber
-fkMakeID foreign key to tblMakes
-txtModelName

tblAssets
-pkAssetID primary key, autonumber
-fkAssetTypeID foreign key to tblAssetTypes
-fkModelID foreign key to tblModels
-VehicleYear
-dtePurchased (date purchased)
-fkUnitsOfMeasureID foreign key to tblUnitsOfMeasure (defines whether the usage will be tracked via hours or mileage)

tblUnitsOfMeasure (2 records hours or mileage; can add more if needed later)
-pkUnitOfMeasureID primary key autonumber
-txtUnitOfMeasure


Since you have many key numbers associated with an asset (one asset to many key numbers relationship)

tblAssetNumbers
-pkAssetNumberID primary key,autonumber
-txtKeyNumber
-fkKeyNumberTypeID foreign key to tblKeyNumberTypes

tblKeyNumberTypes (3 records S/N, VIN, Asset #)
-pkKeyNumberTypeID primary key, autonumber
-txtKeyNumberType


Since an assest can have many drivers/operators over time, that is another 1 to many relationship

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblAssetPeople
-pkAssetPeopleID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkPeopleID foreign key to tblPeople
-dteEffective (when the person became responsible for the asset)

Regarding this: vehicle specific repair info

I would have a table that holds a generic list of the items and then associate them with the asset

tblRepairItems
-pkRepairItemID primary key, autonumber
-txtRepairItemName

tblAssetRepairItems
-pkAssetRepItemID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkRepairItemID foreign key to tblRepairItems
-txtSpecificReference (hold the actual filter #)

Regarding the specific items, can the filter # be different depending on from which vendor you buy it?

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]

mileage or hours [depending on equipment]

Since this is dependent on the asset, it goes in the tblAssets

tblAssetUsage
-pkAssetUsageID primary key, autonumber
-fkAssetID foreign key to tblAssets
-spUsage (a single precision number field to capture the mileage or hours of use)
-dteUsage A date field to capture the when the usage occurs. I'm not sure how detailed you want to be here. You could use this as a period ending date. You would define the usage period length somewhere else (asset table if it is dependent on the asset)

weekly mileage or hours [depending on equipment], monthly usage [based off of weekly mileage], monthly iron days [hours per month divided by 8]

These are all calculations and thus are generally not stored in the table; you would calculate these on the fly when needed in forms, queries and reports. You will probably need a query to do the actual calculations.

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.)

I would set up a table that has all possible preventative maintenance tasks. An asset can have many PM tasks and the same tasks can apply to many assets each with a specified interval

tblPMTasks
-pkPMTaskID primary key, autonumber
-txtPMTaskName

tblAssetPMTask (table that defines the tasks required for the asset and the interval at which the task must be done)
-pkAssetPMTaskID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkPMTaskID foreign key to tblPMTasks
-Interval

You would calculate the schedule based on the interval and usage of the asset and when the maintenance was last conducted. So we need a table to hold when maintenance occurs


I will assume that during a PM event many tasks might be completed

tblPMEvents
-pkPMEventID primary key, autonumber
-dtePMEvent (date of the event)
-fkAssetID foreign key to tblAssets
I think you will need either a number (mileage/hours) or a reference to tblAssetUsage here so that you tie when the PM events take place; alternatively, you might be able to just go by the dates??

tblPMEventTasks
-pkPMEventTaskID primary key, autonumber
-fkPMEventID foreign key to tblPMEvents
-fkAssetPMTaskID foreign key to tblAssetPMTask

SOS[Scheduled Oil Sampling] (asset #, taken from [engine, front/rear diff, hidraulics, radiator, etc.], sample date, mileage/hours, result [ok, monitor, action], action taken)

Would this just be considered as a specific PM item?

If not, then I woud have a table that holds the fluid names and relate those to the sampling event

tblFluids
-pkFluidsID primary key, autonumber
-txtFluidName

tblSOSEvents
-pkSOSEventID primary key, autonumber
-dteEvent date of event
-fkAssetID foreign key to tblAssets
I think you will need either a number (mileage/hours) or a reference to tblAssetUsage here so that you tie when the PM events take place; alternatively, you might be able to just go by the dates??

tblSOSEventItems
-pkSOSEvItemID primary key, autonumber
-fkSOSEventID foreign key to tblSOSEvents
-fkFluidID foreign key to tblFluids
-Result

If a problem is found you will need to track action items relative to the event/item combination

tblSOSEventItemTracking
-pkSOSEvITrackID primary key, autonumber
-fkSOSEvItemID foreign key to tblSOSEventItems
-fkActionID foreign key to tblActions
-dteAction

tblActions (a table that holds all possible actions that could take place)
-pkActionID primary key, autonumber
-txtAction
-fkCategoryID foreign key to a table that holds whether the action is applicable to an SOS event or a Work Order (see below)

tblCategory
-pkCategoryID primary key, autonumber
-txtCategoryName

Work Orders (asset#, WO#, status, opened by, date written, date received, description of problem, priority, response due date, date responded, action taken, date closed)

Is a work order necessary for PM items as well as the SOS? If so then perhaps it can be integrated into tables above.


If not...

tblWorkOrders
-pkWorkOrderID primary key, autonumber
-fkAssetID
-txtProblemDesc
-txtPriority
-fkPeopleID foreign key to tblPeople (opened by..)
-dteDue (due date)

Since you have many actions (or dates associated with certain actions) for a work order, you need a table to hold the types of actions and a table to hold the dates relative to a work order


tblWOActions
-pkWOActionsID primary key, autonumber
-fkWorkOrderID foreign key to tblWorkOrders
-fkActionID foreign key to tblActions
-dteWOAction a field to hold the actual date

The following would actually be considered as actions relative to the WO:
written, received, responded, closed

[10 Gauge]

Wow thanks a lot for laying that all out for me. That has given me such a better understanding of how everything is linked together. I started making tables and setting up fields and I got lost when I got to the 3rd table because I couldn't figure out how I was going to relate it to the other tables.

What you have given me is HUGE. I am going to try and start building the new database tomorrow when I get in to work, only have 20 minutes left on the clock today but I want to get this project under way and see if it's something I am actually capable of putting together. If it is, I will have saved the company over $1000 as these premade fleet management software suites are running well in to the $1000+ category and don't do exactly what we need done.

[jzwp11]

It is just a start, there are things that still need to be refined and some consolidation might be in order. I'll give you some time to digest and play around with it. Please post back with any questions.

[10 Gauge]

I have a quick question jz. When I am using the lookup wizard, for example when creating the foreign key between the table models and makes, in the lookup wizard for MakeID would I be linking that to MakeName or MakeID on the Makes table? Or is it just preference? I was thinking if I linked it to MakeID then the person selecting would have to know what the ID # was of the different makes, so can it rather be linked to MakeName?

[10 Gauge]

Oh yeah one other quick one. On the AssetNumbers tables with the link to KeyNumberTypes. You have shown S/N, VIN, and Asset #. I am assuming so that you can choose one. Each piece of equipment has an Asset # regardless, so would this then be added as it's own field on the AssetNumbers tables, and making the KeyNumberType table only displaying S/N or VIN?

[jzwp11]

When I am using the lookup wizard,

If you are attempting to use lookups at the table level, that is not recommended. Please see this site that details the problems that can be caused by lookups at the table level. Lookups (combo and list boxes) are best left for your forms.

All joins between related tables should be made via the pk...ID and its corresponding fk....ID. I've attached an example database. Please take a look at the relationship window to see the joins between the tables.

[jzwp11]

Oh yeah one other quick one. On the AssetNumbers tables with the link to KeyNumberTypes. You have shown S/N, VIN, and Asset #. I am assuming so that you can choose one. Each piece of equipment has an Asset # regardless, so would this then be added as it's own field on the AssetNumbers tables, and making the KeyNumberType table only displaying S/N or VIN?

Since you will have at least 2 and possibly 3 identification numbers associated with an asset, you have a one-to-many relationship which would require a separate but related table that holds all of the identification numbers.

[10 Gauge]

Ok thanks, I deleted all of the lookup wizard stuff and manually added the relationships.

Will you please look at what I have so far just to see if it looks like I'm on the right path? TIA!!

[jzwp11]

I'll have to look at it tonight at home since I only have Access 2003 here at work.

[10 Gauge]

Ok thanks. I will keep working on it and repost an updated copy here when I get to a good stopping point for the day.

Thanks again.

[10 Gauge]

Here's what I've got so far (disregard the forms, I am just playing there to see what the data looks like on a form....) Thanks for looking.

[jzwp11]

I had a look at your database. The only thing I noticed was that for the two event tables you had joins between those tables and both the asset table and the asset usage table. Since the asset usage table is already tied to the asset having those tables join to the asset table was redundant. I have cleaned those up and have attached the modified database. I also keep thinking that the SOS is just a type of PM, so I really think they should be combined, but I'll leave that call to you.

[10 Gauge]

Thank you jzwp11. The SOS is currently recorded and reported on it's own sheet apart from PM service and it's a pretty big thing that the project managers have to keep up with aside from standard PM service as well.

Ok so here is another question as I have been playing around with the forms a bit for this database structure. If you have a look at my "Control Panel" form which is going to be the AutoExec form eventually when this is setup. On the "View Equipment Details" form that I am planning to build I will pretty much need to be putting data from almost every form on that form. That form will be the equipment specific information and everything related to it. I had problems adding data from the different tables to it when I tried. One example was it threw up a relation error when I tried to add the current operators first/last name to the form. It seems to work alright if I add the ID numbers of the parent tree's to the form first, however those ID's aren't necessarily used for anything that anyone needs to know about, just for the database purposes. Is there a way to be able to add child table information to a form without first having to place the parent tables ID on the form?

[jzwp11]

You will need to use a main form/sub form design because of the relationships that are now established. The main form would be based on your asset table (the one side of the one-to-many relationship) and the subforms will be based on the related tables (the many side of the one-to-many relationship). You will need a subform for each related table. Generally I do this using tabbed subforms since it usually gives a more organized look. Also, you will need combo boxes to select certain look up values. I've attached the example database I provided earlier with a form/subform that illustrates my suggestions.