Changing prices

J

Joel

I'm creating an orders database where an order for a
particular part would be executed at different prices over
a period of time. If the part # does not change, how do
you setup or query the dbase to show orders at one price
through x date and at another price starting when the
price changes. Thanks in advance.
 
W

Wayne Morgan

You would need a table with the dates that the price changes, the product
code, and what that change will be. The change could be a multiplier with
the original price in the product table or the change could be the actuall
prices on a given date. How this is set up depends on how your changes are
implemented. If the latter option, you could link the two tables in a query
on the product id field and choose the date field based on the current date
to get the current price (Max date where date <= current date). If the first
option, you would do something similar, but then multiply that value by the
original price in the products table.

If the change is static (after 10 days add 10%, after 20 days at 15%, etc)
this could actually just be done by making the price field a calculated
field in the query using IIf statements to determine the multiplier based on
the number of days since the date the product was added to the table.
 
J

Joel

Thanks. That answer would seem to work if I was looking
up the current price, but what if I was reporting on a
month's worth of transactions? How would you get the
system to report one set of transactions at one price and
those coming after it at another?
 
W

Wayne Morgan

If you know the "rule" for the price increases and the date that the
transaction was made, you can always apply the rule based on the date of the
transaction, not the current date.
 
Top