Change a product price in my Products table without changing previous invoices...

[Mel1973]

Hi,
i have only a little experience with MS Access,
i am trying to build an invoicing access database and i have already my Tables , Employs, Customers, Invoices, invoice_details.....
and my forms and some queries...
the problem is: every time i change a product price in my products table, this change will affect all my previous invoices.
i spent a lot of time to solve this issue, i did not succeed.
can you help please ?
thank you all in advance.

[mike60smart]

Hi Can you upload a zipped copy of the database?

[ranman256]

the invoice should have made a copy of the prod.price, not linked to the price table.
this way that price is locked in and unaffected by the price table change.

when user picks the product, tInvoice.price = tProd.Price

thus when the price table changes only NEW orders will get the new price.

[CJ_London]

ranmans suggestion is the usual solution. Another is to have a price table with an effective from date - you can then find the price that was effective on the date of the invoice. However I would only use this method if I needed to keep a history of prices and for performance reasons I would still probably store the price in the invoice (together with other looked up variables such as discount or VAT/Sales Tax)

[Mel1973]

hi ,
Thank you all, for your answers,
i am trying to upload a copy of my database, it did not work, i will try again later.
Ajax i do not need to keep a product price history,
Ranman, the invoice should have made a copy of the prod.price, not linked to the price table.
how can i do this?

[CJ_London]

i am trying to upload a copy of my database, it did not work

remove all tables, forms, queries, report not relevant to your question. Remove surplus data leaving enough to demonstrate the problem - or replace with example data if the real data is confindential. Then compact/repair and finally zip the file and upload the zipped file

[orange]

See this link for info on AgreedTo Price and prevent loss of historical prices.