MS ACCESS 97 1

[GGORM]

I have a project for my boss.
It begins with keeping track of gallons of fuel used, mileage/hours for each piece of equipment and date entered.
My problem is this.
I have different categories of equipment: Backhoes, bulldozers, trucks, payloaders etc.
Within each categories there is different types of equipment ie: truck#s 132,131,160,161 and so on. Different types of backhoes JD410,JD510.
Payloaders: CAT980,530,220.Backhoes:ex400, ex450 h-16,and so on.
Which would be the best way to design my database because I have tried to do this before with a table for each equipment category and It is very hard to report on these tables for they all have pretty much the same fields within each table. Does anyone have any suggestions to setup a database similiar to the one I am referring to.
Your help would be greatly appreciated.
GGORM GGORM

 

[Jukles]

I'm definatley now expert here, recently I was faced with a similar problem and I set up a link table in.

Main Table Link Table Product Table

What ever the Primary to main Model ID
driving table is Item ie Truck 12M Item ie truck M21

I hope this gives you some idea

Julie
Julie Carney

 

[JerryDennison]

Actually you have a classic one-to-many relationship between the usages(fuel, mileage, hours, date, etc...) and the piece of equipment the transaction is for. This is a separate issue from trying to group the equipment by category.

Let's tackle the category issue first. Unless your equipment can belong to more than one category you should simply place this as a field in the equipment table. This field could use as it's source another table's primary key(i.e. have a foreign key to a list of equipment types from another table). By doing so you can have a much more detailed description of the type of equipment without taking up a lot of real estate and at the same time insure that only those equipment types you've set up are used. In other words, prevent one user from calling an item a bull dozer while another calls it a dozer, etc...... Once you've assigned a piece of equipment the group it belongs to, reporting is very easy and efficient.

Tracking fuel consumption and mileage is best handled via another table linked to the equipment table. This table would have a foreign key link to the equipment table's primary key. It should have the relationship between the two tables set for referential integrity, cascade update/delete. This table should also have only those fields that are specific descriptors of the consumption transaction, don't include any descriptions of the equipment these belong in the equipment table.