Need Advice re: Storing Data 2

[darrelhawes]

I am designing a simple database to track equipment usage information. We maintain a fleet of construction equipment that is charged out to construction projects based upon hours used, subject to a minimum amount of hours. The minimum amount of hours is different for each piece of equipment.

I have a table which lists the following information:
Equipment Number
Equipment Description
Rate
Minimum

I have a form built to input the usage data. Included on the form is a combo box in which the user can select the equipment number.

Here's my quandry:

In my "Transaction" table, is it better to:

A. Store the Equipment Number, usage amount, rate, and minimum, in which case the computation of amount charged for that equipment can be computed on reports, or

B. Store the equipment number and amount charged.

I realize that a third option could be to just store the equipment number and usase hours, and then look back at my equipment table for the rate when printing reports. However this is not a viable solution since some of these construction projects span several years and the rates will change during that time frame.

Darrel

 

[kpal29]

I would design it like this:

Table: Equipment
Fields: Equipment ID, Equipment Name, Equipment Description

Table: Rates
Fields: Equipment ID, Eff. Date, Exp. Date, Rate, Min.

Table: Transactions
Fields: TranID, Equipment ID, TranDate, Rate, Min.,Hours, Amount Charged, any other field you need here.

Then create an update query that will automatically update the rate, min and amount charged by using the rate that matches the equipment ID and whose Tran Date is equal to or greater than Eff. Date and whose tran Date is equal to or less than Exp. date.

 

[quest4]

Good structure kpal29, and I would take it just a little further. In your you eq table I would make the EquipmentID the Pkey, the in relationships I would setup a one-to-many between the EquipmentID of Equipment and the EquipmentID of Transactions. Then you will have to determine if your rate relationships are on-to-one or one-to-many, and setup that relationship. Then setup the queries. Good luck.

 

[darrelhawes]

Thanks so much for both posts! Very helpful as I am still learning the basics.