Get A Max Value From One Table Base On Date From Another.

I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.

Say I have a table with items, prices, and dates.

PriceTable

ITEM PRICE DATE

A 1.00 5/5/2000

B 3.00 1/1/2000

A 2.50 6/5/2004

....

This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.

Now say I have a table of transactions with items, amount bought, and dates.

TransactionTable

ITEM AMOUNT DATE

A 5 6/6/2003

A 1 8/5/2003

A 2 8/5/2004

The total for A should come out to be 11.00. There are multiple Items and multiple price changes.

If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.



My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".



I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".

Hope that made sense, thanks.