Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Calculated Values into a table (Windows 98/Office 2000)

    We make a product and log the number sold to a client as a series of transactions with the date and the number sold. I have attached a very simplified version of the database to this post as a zip.

    One table holds just the price and the other all transactions. So if we change the price, we simply alter it in the table.

    Each transaction is entered in a subform (FrmInputForm), which gets its data from a query (QryInputQuery). Another Query has everything from the first query and a field called value (QryForInvoice) which is the Qty sold times the Price. This query is used to print an invoice.

    This all works very well, but would be very much better if the value of the order could be stored in the table as well.

    The problem being that we cannot get a true value of the orders made by a particular client, once we change the price as the new price is then used to calculate the value of all the old orders. If the Value were stored in the table with the qty and it would be a simple matter just to sum the value column.

    So my problem is

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding Calculated Values into a table (Windows 98/Office 2000)

    Yes you can, in fact if you wish to keep the costs of each transaction you must put this in the table or the extended cost as you are suggesting.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Adding Calculated Values into a table (Windows 98/Office 2000)

    General advice is not to store anything that can be calculated from other fields.

    Howeber you do need a record of how much you sold the things for. I usually put a selling price field in the sales transactions table, with the value from the inventory table being used as the default value.

    Using the combination of actual selling price and qty you can calculate extended price and invoice total when you want to.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Calculated Values into a table (Windows 98/Office 2000)

    Hi Pat and John,

    Sorry for the delay in replying, but I went away.

    I agree with the advice not to store anything that can be calculated, but if you want to know what a customer historically was charged before the price changes I can't see any other way. To keep everything simple in the post, I didn't mention there is a discount structure that changes the price as well.

    But what I want to do is as Pat suggests and put the extended price in the table and this is I'm not sure how to do - obviously I don't want to type it!

    Thanks for your help

    Michael

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Calculated Values into a table (Windows 98/Office 2000)

    You'll need to do this in the form, there is no way to do this in a table or query. This should not be a problem, since an end user should never edit a table or query directly, only through a form. Include the Value field in the record source of the form, and write the calculated value into the table in the After Update event of the Qty text box:

    Private Sub Qty_AfterUpdate()
    Me.[Value] = Me.Qty * DLookup("Price", "TblUnitPrice")
    End Sub

    BTW will your table TblUnitPrice always have only one record? If it will have more, you'll need a field to link TblUnitPrice to TblQtySold (for example a ProductID)

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding Calculated Values into a table (Windows 98/Office 2000)

    In addition to what Hans has suggested you may want to think about putting a date in the cost table (tblUnitPrice as Hans has suggested) that denotes when the cost is applicable from.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Calculated Values into a table (Windows 98/Office 2000)

    Once more everyone thank you so much for your help. I really do appreciate your support.

    With very best wishes

    Michael Peak

Posting Permissions

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