Results 1 to 7 of 7
  1. #1
    Mel1973 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    2

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

    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.

  2. #2
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    263
    Hi Can you upload a zipped copy of the database?

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,997
    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.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,995
    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)

  5. #5
    Mel1973 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    2
    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?

  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,995
    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

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 05-06-2018, 09:43 PM
  2. Replies: 17
    Last Post: 12-14-2015, 10:23 PM
  3. Replies: 2
    Last Post: 08-22-2014, 05:16 PM
  4. Replies: 4
    Last Post: 04-26-2013, 08:32 AM
  5. Replies: 8
    Last Post: 04-24-2012, 01:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums