Match transaction date with Price history

[Guest]

Two tables -- Transaction table with Trxn_Date for each record, and Price
History that lists effective date and current price. I need to match the
most current price in Price History table to the Trxn_Date.

Trxn_Date
01/01/2004
01/20/2004

Price_History
12/10/2003 $10
01/18/2004 $11

The 01/01/04 Trxn record should be priced at $10 and the 01/20/04 record
should be priced at $11. My queries using Price_History date <=Trxn Date
returns two results for the 01/18 record i.e. $10 and $11 instead of just the
$11.

 

[Dale Fye]

Steve,

There are several ways of doing this, the "best" one will depend on your
indexes, the size of the two tables, and other factors.

1.
SELECT T.Trxn_Date, PH.Price
FROM Transaction T, Price_History PH
WHERE PH.PriceDate = DMAX("PriceDate", "Price_History", "PriceDate <= #" &
T.Trxn_Date & "#")

2. The second method uses a nested subquery which determines the maximum
PriceDate less than or equal to a transaction date. It then uses this
sub-query to link to the price_history table to retrieve the appropriate
price.

HTH
Dale