Lookup with table

[Guest]

Hi,

I asked this question yesterday - but when I got a response and looked at my
question I realize I did not say what I meant. Sorry!

Here is my question:

I am trying to build a query that uses two tables.
Table 1 is just a
Column1 = policy number: 1-10,000
Column2: IssueDate: Between "mm/dd/yyy" between 1980 and 2007

Table2:
Issue Date: same data format as table 1
Charge: Vaires by quarter

So charges from 1/1/2007 to 3/31/2007 might be 0.75 and from 4/1/2007 -
6/30/2007 charge = .95.

So if the issue date is 2/8/2007 I would want the charge to be 0.75.
Because 0.75 is the charge in the 1st quarter of 2007.

In the table the dates range from 1980 - 2007. So there are a lot of dates.

I want to create a third table - with Table1's issue date, policy number and
a 3rd column - the "charge" that varies by quarter.

Can this be done with a "lookup table" efficiently?

Thanks for your help on this!

 

[Douglas J. Steele]

I don't think you've explained it any better this time! <g>

Will Table2 have 4 rows for every year between 1980 and 2007?

Try creating a table with 3 fields: StartDate, EndDate and Charge:

StartDate EndDate Charge
10/01/2006 12/31/2007 0.55
01/01/2007 03/31/2007 0.75
04/01/2007 06/30/2007 0.95

and so on.

You do not need (nor want) a third table: simply create a query that joins
the two tables together, and use that table wherever you would otherwise
have used Table3:

SELECT Table1.PolicyNumber, Table1.IssueDate, Table2.Charge
FROM Table1 INNER JOIN Table2
ON (Table1.IssueDate BETWEEN Table2.StartDate AND Table2.EndDate)