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

Oh, and the quantity would have to be considered too.