Hi - I hope someone can help with a problems thats been puzzling me for some time.

I have a table which holds a list of customers, the items they buy, the price they pay, and the date at which that price becomes effective. The table will hold past prices and effective dates and also those in the future still to come into effect. Customers can take the same items but may have different prices and effective dates.

The price will change when the next effective date comes into force. If there is not a new date then the price at the last effective date stays in force.


Customer   item      price       date
1               abc       10        Jan 2009
1               abc       20        Jan 2010
1               abc       30        Jan 2011
2               abc       15        Jan 2009
2               xyz       5          Jan 2009
2               xyz       7          Jan 2010
2               xyz       9          Jan 2011

For each customer / item combination I need to be able to determine which is the the current effective price, EG the price at 24 Sept 10 for abc would be 20 for Cust1 and 15 for Cust 2. xyz would be 7 for cust 2.

I need to be able to extract the current effective price as I need to display it in another report via a subreport.

I have tried various formualae, datediffs, max dates etc but none work 100%.

Any help much appreciated

Ian S

Hi - thanks for your reply

Sorry no, there are not beginning and end date fields as such, just one field - an effective date - the date the new price starts to be used.   

Unfortunately no !

An effective date can last for any time period, a day, a month, ten years. An effective date only ends when a new date is entered onto the table and that date becomes due.