>>I have a problem that I originally thought was simple, but somehow became complicated....
>>
>>I have two tables, both of which are out of my control, that stores invoices in one and the product cost that is effective from a certain date in another.
>>
>>The structures are similar to:
>>
>> Invoices - inv no, inv date, part no, quantity, etc...
>> Price list - part no, price, effective date
>>
>>What I need to do, preferably in a query or set of queries, is to find the invoice value of all invoices between a certain period. This means that I must determine the correct value of each product when it was sold.
>>
>>Any ideas, even the solution, would be gratefully received.
>>
>>
>>Jason
>
>If I understand your question correctly then it should be:
>Select SUM(Price) as TotalPrice, Invoices.Part_No ;
>From Invoices,Price_List ;
>Where Invoices.Part_No=Price_List.Part_No ;
>And BETWEEN(Price_List.Effect_Date,dDate1,dDate2) ;
>Group by Invoices.Part_No

Unfortunately this would give me the total of the list prices that were effective over a period...

The invoice date (7 Jan 98) must point to one specific effective date (1 Jan 98). My problem is that I can not detect the correct effective date, and determine the correct price. It would be no problem if the effective date was a range period, but it is only a starting date.

It would be so simple if the list price was copied against the invoice line when it was invoice. People like to make things complicated...

Jason