Changing prices

  • Thread starter Thread starter Joel
  • Start date Start date
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.
 
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.
 
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?
 
While it can be done and follow a relational model, this is a place where I
stuff the actual cost at the time of the transaction into the transaction
record.
When the transaction is finished it should be treated like a piece of paper
and any change would be done on that transaction manually.

I suspect that this is a collusion point between the relational model,what
an accountant would want and what a "normal" person would want.
 
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.
 
Back
Top