I have done a data base with several files (11) relating each other, works as intended. I am lost at one point. Here is what I done and what I need to do.

My creation has files Customer, Orders, Line item, Product Code, Product History, Products, Unit book price, shipping and Invoice etc. This works great, we have been using it for few months. One problem seems to be that we have over 3000 products in 35 categories and the unit price is based on qty.

Currently unit price enters accurately when the item and qty entered, calculates fine with all other related charges. However, when we make changes to master unit price file all old invoice reflects the new price, do not stay as created/billed, accounting nightmare.

How can I prevent this, I have to keep the old order/invoices as is and be able to change the unit price. The unit price contains over 1000 fields.

Ah, relationships and related values are wonderful things, but there is still a place for lookups. An invoice file is a good example. You can bring the customer name and address across using relationships, but lookups are more appropriate for just the reasons you have cited. When the client's company name, phone or address changes in the future, you don't want to update this information on the invoice. The invoice is a historical document and must contain the information in place at the time of the sale.

I'm just in the process of writing yet another order, invoice, inventory system for a customer and this concept is key in the design. There are many, many places where data is historical and lookups are used instead of relationshps.

To make a long answer only slightly longer, use lookups from the price file into the invoice (or invoice transaction) file so that prices are preserved.

-bd