>>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

The problem is, as far as I understand it, is that we don't have dDate1 and dDate2.

Depending on the size of the tables, I might make a copy of the price list table, add a date field that specifies the no-longer-effective date, fill-in that field (which would be determined by the next effective date-1) for all that apply (ie. leave blank for the records that are the last effective date for that part). Then Ed's suggestion with

(BETWEEN(Invoices.inv date, Price_List.Effect_Date,Price_List.Until_Date) OR
(Invoices.inv date<=Price_List.Effect_Date AND empty(Price_List.Until_Date))
)

Of course, if this is real slow, it might not be a good idea.

Joe