Results 1 to 11 of 11
  1. #1
    Join Date
    May 2010
    Posts
    23

    Unanswered: Help Deriving a Field

    So I'm new to Access and need to build a database for a small business. The business provides items to various dealers. I have set up tables so that the "items" table has product cost and "item_orders" table has quantity.

    The problem comes in the "orders" table that has a field called Total_Cost which should calculate the total order price by multiplying PRICE times QUANTITY.
    I need to know how to make this Total_Cost field multiply the other two fields - How do I do this?

    I have read that it is unadvised to have a derived field inside a table and that I should only do this operation in a query. However, with that thinking, old orders data would become invalid whenever the price of an item changes.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It is pretty common to store the price applicable to a given sale along with quantity in the orders table. An alternative is to have a price table that includes effective dates, but most prices are too volatile for that to be practical.
    Paul

  3. #3
    Join Date
    May 2010
    Posts
    23
    Can you give me a brief tutorial on how to do this? Not sure how to set the automatic field value to be Cost*Quantity.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As you've said, you don't put a calculated field in the table. In a query, form or report you multiply quantity times price.
    Paul

  5. #5
    Join Date
    May 2010
    Posts
    23
    It is pretty common to store the price applicable to a given sale along with quantity in the orders table
    Please explain this.

    you don't put a calculated field in the table.
    Okay. But then how do you store historic order costs in the query? Doesn't the query recalculate all order costs including historic ones where an item price may have changed - therefore rendering it useless data?
    Last edited by sirrip; 05-08-10 at 06:18.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You've answered your own question by quoting Paul!

    You don't stored the calculated value in the table, but you store the current cost per unit at the time of the order in the table. To get the cost at the time of the purchase (historic order cost) at a later date, you simply multiply quantity times the stored current cost per unit.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    May 2010
    Posts
    23
    hmmm...
    So let's say the ITEM table has an item called XBox 360 for $300. Over time, the price of an XBox 360 changes to $250. I assume, to reflect this change in price I would not go into the Price column, delete 300 and insert 250. But now when I do my query, won't all the old order records for customers buying the item at the old price be recalculated using $250 -- and therefore give me incorrect data?

    Or are you saying that I create an additional XBox 360 record in the item table, so that when the price changes, I just create a new record for XBox 360, ultimately giving me two. One for $300 and one for $250.

    Hope this makes sense.
    And sorry for being so slow.
    Last edited by sirrip; 05-08-10 at 18:03.

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I do not know how much simpler this can be said;
    1. At the time the order is made, you pull the current price from the table
    2. You store the current price of the item as part of the order record
    3. In the form or report (not in a query) you calculate the cost of an item (Quantity x Price)
    4. If, later, you need to review the order record, you use this price, stored as part of the record to re-calculate the cost in the same manner as in #3.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    May 2010
    Posts
    23
    Double-Post
    Last edited by sirrip; 05-08-10 at 19:36.

  10. #10
    Join Date
    May 2010
    Posts
    23
    Are you saying to put a column in the ORDER table that pulls price per unit from the ITEM table at the time of the purchase? How do I do this in such a way that this column will not be subject to change when the ITEM table's price field changes?



    Like I said, I'm totally new to this. It would help if you specified in your post above which tables you are referring to. And even more helpful if you put it in terms of the Xbox example.

    I truly thank you for your help.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry to interfere but the problem is beyond the scope of your database structure: it also depends on your business plan and how you manage the stock value.

    - On January 1rst you buy 5 XBoxes @ 300 $ a piece and enter them into the stock.
    - On February 1rst you make an inventory: 3 XBoxes were sold and you reorder 3 XBoxes at the current price which is now 250 $ a piece.

    What's the value of an XBox in your stock now?, when you sell the next XBox is it one you bought @ 300 $ or one you bought @ 250 $, and at what price will you sell it?

    Depending on how you manage such situations, the structure of the database can be slightly different.
    Have a nice day!

Posting Permissions

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