Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Storing Prices/totals in tables (Access 2k)

    I need a little advice concerning a database I am working on. I have a form which will allow the user to input information regarding a new order. I have several fields for Subtotal, (which pulls totals from a subform which calculates Qty times Price), sales tax, shipping charges, deductions. I use the following control source for an unbound control which calculates the order total:

    =([Forms]![frmOrder]![frmItems].Form!Order_Total+[txtSalesTaxAmt]+[txtFedTaxAmt]+[txtShipCharges])-[txtDeductions]

    I would like to store this value in a table called tblOrders, which has a field called OrderTotal, Because if the product prices change, I don't want the total of an old order to change.

    In typing this post, maybe I should utilize a different approach regarding the pricing, so that the subform checks the order date and then calculates the correct price for the product at the time the order was created? Is there a standard method for handling this type of situation.

    For example, a year from now, if the product prices have changed, all of my order totals will change utilizing the current price, instead of the old price.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Storing Prices/totals in tables (Access 2k)

    Do you have an OrderDetails table that stores each individual item in an order? If so, then just make Price a field in that table, along with Qty. Then you can recalculate it at any time and get the correct value. If you don't have an OrderDetails table, then I strongly suggest you think about it. An Order should hold information about who placed the order, the date, etc., and possibly the applicable tax and shipping. The details table should have one record for each item in the order and should link to the Order table on the primary key for Order.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing Prices/totals in tables (Access 2k)

    Marc
    You should not store the total in the table. Instead, you calculate it with a query whenever you need to display the total. By storing the individual items instead of the total, you will always know that the current calculation is correct.

    Also, you will need to store the price and the quantity of each item in the table. This may seem contrary to normal advice to look up the price in an "items" table, but in your case, the price is fluctuates, therefore, it needs to be stored separately.

    You should still have an "items" table with a field for price, but you would use that as the default value for the actual sales price for each order. In other words, when you initiate the sale of an item, you would look up its default price in the items table and copy that value to your "orders" table. Then you would allow the user to make any necessary corrections such as volume discounts, etc. When you modify the price in the "items" table next year, it will not affect any of the existing orders.

    Make sense?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing Prices/totals in tables (Access 2k)

    Yes, Charlotte I have a details table, mine is tblItems, which does record the individual items and links those items to the individual Order. I am having trouble at this point in storing the price in the item table. Currently the subform in the frmOrder form automatically displays the name description and price of the item in the subform on frmOrder. However, the price is calculated in the subform's query using an IIF statement which determines whether to use the wholesale or retail price. I think my problem is in writing the result back to the price field in tblItems. I think if I can accomplish this, then the problem will be solved.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing Prices/totals in tables (Access 2k)

    Ok, I figured out the problem and have solved it. Since Price was calculated in the query of the subform I could not save it automatically in the field Item_Price in tblItems. Under the after update property of the Prod_ID I ran code : Me.Item_Price=[txtPrice]. After testing this several times, it will store the price and maintain the proper price when looking up orders once the price has been changed, resulting in the proper order total when viewing all orders.

    Thanks for your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •