How to 'fix' a value of a record when using it to make new ones

[Sander_Kaa]

Dear all,



I need some help on my database. It's used for administration of membership of my scout-group. Furthermore, invoicing of membership-fee is done with the db.

My database has the following tables: members, products, orders.
'Members' contain names and adresses of our member.
'Products' contain the products we use over and over again (e.g. a record 'membership fee january' and 'summercamp' exist) and have a unique identifier and price attached to each record (within the table).
'Orders' contain the order-ID (the unique identifier), member-ID,
product-ID, a date and a field to mark if the order has been paid. This table has a relationship to Members and Products.

By using a form I can add records to the 'Orders' table by selecting a member in combination with a product. Via a report an invoice is drawn up with the members adress, the product(s) and price.

Now comes the problem: when after eg. a year I change the price of one of the products, all existing records in the 'Orders'-table also have a new price (they simple follow the relationship and take over the new price). I want to have existing recors in the orders table to keep using the original price of the product (the price on the moment the order was created).

To make it more complex I want to use standard access functionalities (and not VBA). The db will not always be maintained by experienced users so the KISS (keep it simple & stupid) principle should be taken into account.

Your help is highly appreciated!

[weekend00]

you should add a [price] column in the [order] table. don't get price from [product]table.

OR

you need to create a new product if the price change,because it should be a new product while any property (the price) changes .