Using Dates to select the proper rate that is active on that date.

[Go_Bears]

Trying to keep it simple. I have a rate table. It contains two key fields
Key1 and key2. It also contains the start date and end date for the rate it
holds. The transaction record (table) has Key1 and Key2 and a transaction
date and the Net sale amount that we need to multiply by the proper rate for
that date. I use a query to bring things together. And things were working as
long as I had only one rate record per Key1 and Key2. But now I am trying to
add the start date and end date logic to allow me to keep old rates and be
able re-run and re-reprice with the proper rates. So how should I incorporate
the start date/end date logic? In the query for the report that calculates
using the rates? When it works it should allow a different rate per day for
key1/key2. So at month end I could handle multiple rates in the monthly
report March 1 through march 31. My date format is the short date MM/DD/YYYY.

I have tried searching for someone else that has crossed this bridge before
me, if there is a example that I missed, please point me to it.
Thanks
Go_Bears

 

[KARL DEWEY]

Join the two tables on Key1 and key2.
Use this criteria on your [transaction date] --
Between [start date[ and [end date]

Add the Rate to your query output grid.