Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: Table Lookup conundrum

    On a form, I need to look up a price in response to the entry of a product SKU...simple concept. The requirement is that the price never change over time...if they return to this form 3 months from now they want the form to reflect the price that was given the day the form was completed.

    Something like the following will retrieve the price from a source table:

    SELECT TestOrder.SKU, TestLookup.Price
    FROM TestLookup INNER JOIN TestOrder ON TestLookup.SKU = TestOrder.SKU;

    But once the underlying prices are changed(guaranteed), the form will reflect the updated price.

    I need to find a way to set the price permanently. I thought about copying and pasting the values into a separate control, but that seemed clumsy.

    Any ideas on an approach...
    Thanks,
    Bill

  2. #2
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    you could keep a history of the prices and put the date changed against each change. for new records use the latest price, for old ones use the price which is valid for the date the record was created.

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi,

    Ive seen this issue previously..............

    One way around it is certainly emmas system of keeping sku in one_to situation and price_point and date in a many.

    Questions that arise are -

    is there a chance that a price could be changed during a day so that there would in effect be 2 prices for one date? something which later will cause major problems when interigating a system.

    Could a price and time/date stamp be recorded when an enquiery is made - this will give rise to a bloating of the "stored price file" but does keep to absolute the accuracy of the data.

    rgds
    gareth

Posting Permissions

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