Structure for a Changing Price Lookup Table

[SAm]

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

 

[SAm]

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

 

[SAm]

please help!

i need help in designing the table lookup.

thanks,

sam