date range query between 2 tables

[Ron]

I have 2 tables. One table data that includes an application date and # of
years (ie 1o, 5, 6, etc.). The 2nd table has a value based on a date and #
of years from the first table. I am trying run a query that will take the
value from the 2nd table and combine in with the 1st table data and the
export to excel for addition review.
The problem I am having is the date value from the 2nd table is just 1 date
per month, while the 1st table data is anytime during the month. So
selecting the date is being difficult.

Example Table 1

customer1 application_date Term
mary 01/06/05 5
bob 01/20/05 10
jane 02/01/05 5
alex 02/25/05 15

Example Table 2

Rate_Date 5 10 15
12/15/05 8.67 9.25 8.35
01/15/05 10.25 11.5 8.25
02/15/05 9.25 8.25 7.25
03/15/05 6.25 3.25 9.33

The Application_date (table 1) has to fall between of the Rate_Date (table 2).

So I am expecting tha query to show me:

customer1 application_date Term Rate_Date
mary 01/06/05 5 8.67
bob 01/20/05 10 11.5
jane 02/01/05 5 10.25
alex 02/25/05 15 7.25


Hope this was enough information to help. And any help is appreciated.

 

[gios999]

I have 2 tables. One table data that includes an application date and #
of
years (ie 1o, 5, 6, etc.). The 2nd table has a value based on a date and
#
of years from the first table. I am trying run a query that will take the
value from the 2nd table and combine in with the 1st table data and the
export to excel for addition review.
The problem I am having is the date value from the 2nd table is just 1
date
per month, while the 1st table data is anytime during the month. So
selecting the date is being difficult.

Example Table 1

customer1 application_date Term
mary 01/06/05 5
bob 01/20/05 10
jane 02/01/05 5
alex 02/25/05 15

Example Table 2

Rate_Date 5 10 15
12/15/05 8.67 9.25 8.35
01/15/05 10.25 11.5 8.25
02/15/05 9.25 8.25 7.25
03/15/05 6.25 3.25 9.33

The Application_date (table 1) has to fall between of the Rate_Date (table
2).

So I am expecting tha query to show me:

customer1 application_date Term Rate_Date
mary 01/06/05 5 8.67
bob 01/20/05 10 11.5
jane 02/01/05 5 10.25
alex 02/25/05 15 7.25


Hope this was enough information to help. And any help is appreciated.

 

[KARL DEWEY]

Your table 2 need to be like this --
Term Lo_Date Hi_Date Rate
5 01/15/2005 02/15/2005 10.25
5 02/15/2005 03/15/2005 9.25
5 03/15/2005 12/15/2005 6.25
5 12/15/2005 12/31/2099 8.67
10 01/15/2005 02/15/2005 11.5
10 02/15/2005 03/15/2005 8.25
10 03/15/2005 12/15/2005 3.25
10 12/15/2005 12/31/2099 9.25
15 01/15/2005 02/15/2005 8.25
15 02/15/2005 03/15/2005 7.25
15 03/15/2005 12/15/2005 9.33
15 12/15/2005 12/31/2099 8.35
but you do not have a Lo_date that will fit Mary.
Then use this query ---
SELECT Table_1.customer1, Table_1.application_date, Table_1.Term, Table_2.Rate
FROM Table_1 INNER JOIN Table_2 ON Table_1.Term = Table_2.Term
WHERE (((Table_1.application_date) Between [Lo_Date] And [Hi_Date]));