Solved: Table design problem

[wasim_sono]

Dear All

I have a table "Rate" which consist on rates of different material (Approx. 550 materials).
these rates Change every month. For some calculation we have to retain all records of all months. so how I design table to control the situation. At this time the design of the table is as follow:
Index_no text
Description text
Rate number

If I include a new field namely " Month" then I have to inserted all 550 records for specified month. In next month the procedure will repeat and so on the table populate continuously.
how can I solve the problem.

Wasim

[OBP]

wasim, you need to create a RateID field (type Autonumber) to identify the material, create a second table with the RateID linked to a Rate ID field (type Number Long Integer) in a One to many relationship. Move your Rate number field in to the second table and then add a DateofRateChange field which holds the date that the Rate is Changed each month. This table is never edited only added to with the new rate and new date each time the material rate is changed.
In this way you always know what rate applies to a calcualtion date period. You could even have 2 in the new table, onne for date Rate Efffective From and date Rate effective To.