using more than one field

[Fog]

I have a database (Sales)that has as one of its fields, part number. Its easy to create a second database (Parts) with part number along with part price. The two databases can then be linked/related so as to get part price and determine charge in the database (Sales) based on number of parts needed and cost per part.

However part prices change. I don't want the previous cost calculations to change everytime I change a price on a part number. Seems to me I need effective dates to be added to the relationship to make the part price current and corrrect. Is that possible? Anybody out there have a solution to this type of problem?

[Moon]

quote:

---

Originally posted by JL:

I have a database (Sales)that has as one of its fields, part number. Its easy to create a second database (Parts) with part number along with part price. The two databases can then be linked/related so as to get part price and determine charge in the database (Sales) based on number of parts needed and cost per part.

However part prices change. I don't want the previous cost calculations to change everytime I change a price on a part number. Seems to me I need effective dates to be added to the relationship to make the part price current and corrrect. Is that possible? Anybody out there have a solution to this type of problem?

---

In your Sales database, set the Price field to a lookup using the relationship you describe. Older files will retain the old price that was looked up, while new records will reflect the current price for the part.