Structure for a Changing Price Lookup Table

[Guest]

Hi

i am working now on a a price list table. the prices tend to change. i want
to know what is the best way to structure a lookup table that varies by date.

example:
i have a supplier of services that charges $25.00 as of 1/1/2005. on
3/15/2005 they raized their prices to $27.50. currently i have a table with 4
columns;
ID, TypeOfService, Rate, EffectiveDate

then i run a query that would lookup the rate. and i am thinking: how would
i write the if statement.

Thanks in advance for your help,

sam

 

[Joseph Meehan]

Hi

i am working now on a a price list table. the prices tend to change.
i want to know what is the best way to structure a lookup table that
varies by date.

example:
i have a supplier of services that charges $25.00 as of 1/1/2005. on
3/15/2005 they raized their prices to $27.50. currently i have a
table with 4 columns;
ID, TypeOfService, Rate, EffectiveDate

then i run a query that would lookup the rate. and i am thinking: how
would i write the if statement.

Thanks in advance for your help,

sam

I am not sure what you need, but I suggest that you may want to store
the price as of the purchase date with the purchase information. Store only
the current price in the price table.

Of course depending on the business that may not work.

 

[Guest]

i need to see old prices as well as new prices. i have in one table quantity,
and depending on the date it gets matched up with the current price at that
time. so lets say i have a transaction of 200 quant on march 6, 2002 i need
to lookup the price as of that date. the price might have been updated since
then, as well as i might have changed at an ealier time. so i might have a
price that was set at 1/1/2002 and was again changed on 3/1/2002 and then
changed on 6/5/2002.

sam

 

[Guest]

please help!

i need help in designing the table lookup.

thanks,

sam