Results 1 to 3 of 3
  1. #1
    saudkazia is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2015
    Location
    India
    Posts
    71

    At what stage should Current List Price, Sub Total, and Taxes be added to table

    Taking Northwind as example. At what stage of the transaction should the List Price, Sub Total, and Taxes be added to the order details table.



    So basically the current list price is displayed but this can change at any given time and hence needs to be recorded in the order details. At what point should this be done - When the order is being allocated, invoiced or shipped.

    I messed around the Northwind database to get to where I am with my database so some functionality may have been broken which obviously I need to amend. Also the Northwind Traders database has some frustrations which luckily I have been able to take care of (for instance putting back stock when an order is canceled)

  2. #2
    saudkazia is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2015
    Location
    India
    Posts
    71
    ok. I see the error of my ways now. Instead of the List price being shown as is, there is a function that gets the price. I need to do the same for tax and whatever else I required. Currently I was using a calculated field in the query which would display the field but not add it to the order details table. Changed this and now working.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    On your OrderDetails, record the SellingPrice and Quantity. Do NOT use the Price as it is retrieved from the Products table--because, if you do that, and change the Price in the Product table, the Price on all of your old invoices/orders will change. Instead, store the current price and quantity on the OrderDetails record. This will also let you have Loyalty Programs, Specials, ClearanceSales etc . In reality, this is the Price you charged per Product for this Quantity of Products to this Customer on this Date -- it becomes a permanent record.
    I like to call this the AgreedTo or AgreedUpon Price.

    Good luck.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-14-2015, 08:28 AM
  2. Access suitable for detailed price list?
    By Wozza in forum Access
    Replies: 3
    Last Post: 01-08-2015, 11:21 AM
  3. Replies: 3
    Last Post: 04-23-2014, 12:19 PM
  4. Appending inventory records with current price records
    By sberti in forum Database Design
    Replies: 8
    Last Post: 11-29-2012, 10:24 PM
  5. Replies: 2
    Last Post: 03-02-2011, 01:43 PM

Tags for this Thread

Posting Permissions

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