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

This query is easier if the Price list has a unique id for each record, but I will assume it doesn't based on how I perceive your situation. I am assuming the PartNo is really character and the Effective Date is a date. The key is to use a subquery to find the most recent Price record as of the Invoice date. Once you have identified the characterists of the right Price record (the right EffDate), join it with the Invoice table to get the right price.

SELECT I.InvDate, I.PartNo, I.Quantity, I.InvNo, P.Price, P.EffDate
FROM Invoice I, Prices P ;
WHERE P.PartNo + DTOS(P.EffDate) = ;
(SELECT MAX(P.PartNo + DTOS(P.EffDate)) ;
FROM Invoice I, Prices P ;
WHERE P.PartNo = I.PartNo ;
AND P.EffDate <= I.EffDate ;
GROUP BY P.PartNo)

I haven't checked this and I almost always get them wrong the first time, but the idea is, I think, correct. It you wish to sum up the prices, that's easier.

SELECT I.InvDate, I.PartNo, I.Quantity, I.InvNo, SUM(P.Price) As TotPrice,
P.EffDate ;
FROM Invoice I, Prices P ;
WHERE P.PartNo + DTOS(P.EffDate) = ;
(SELECT MAX(P.PartNo + DTOS(P.EffDate)) ;
FROM Invoice I, Prices P ;
WHERE P.PartNo = I.PartNo ;
AND P.EffDate <= I.EffDate ;
GROUP BY P.PartNo) ;
GROUP BY InvoiceNo