Match Date In TableA With Date Range In TableB

Hi,

I have 2 tables, T_Purchases and T_Discounts and what I hope to do is to use the [ProductID] and [PurchaseDate] fields from T_Purchases in a query that will lookup a discount rate from the T_Discounts table.

The tables and fields are as below:

T_Purchases
[ProductID],[PurchaseDate]
Sprocket1, 24-Sep-2009
Widget2, 31-May-2010

T_Discounts
[ProductID],[DiscountStartDate],[DiscountEndDate],[DiscountRate]
Sprocket1, 01-Jan-2009, 30-Jun-2009, 22%
Sprocket1, 01-Jul-2009, 31-Dec-2009, 20%
Sprocket1, 01-Jan-2010, 30-Sep-2010, 25%
Widget2, 01-Jan-2009, 30-Jun-2009, 14%
Widget2, 01-Jul-2009, 31-Dec-2009, 16%
Widget2, 01-Jan-2010, 30-Sep-2010, 12%

The query output will hopefullyhave 2 records and look like:
[ProductID],[PurchaseDate],[DiscountRate]
Sprocket1, 24-Sep-2009, 20%
Widget2, 31-May-2010, 12%


Any help most appreciated.


Rgds
Growlos

how about something like:
you may have to concat out the # symbol before the [purchasedate] sections of the dlookup function, such that it is:
keep in mind too, that using dlookup() in a query like that will get very very slow if your dataset has quite a few records in it, say....more than 5,000.

You will likely find a non-equi join to be much more efficient. See if this works:

SELECT T_Purchases.ProductID, T_Purchases.PurchaseDate, T_Discounts.DiscountRate
FROM T_Purchases LEFT JOIN T_Discounts ON T_Purchases.PurchaseDate Between T_Discounts.DiscountStartDate and T_Discounts.DiscountEndDate;