Greatest Date Less Than Target Date

[Seti]

I have two tables. Table 1 contains plan code and a target date. In the other table (Table 2), I have plan code and a series of dates for each plan code. I would like to create a query that finds the date from Table 2 that is the greatest of those less than the date in Table 1.

For example:

Table 1
PlanCode Date
123456 1/1/1995

Table 2
PlanCode Date
123456 1/1/1900
123456 5/7/1971
123456 11/3/1990
123456 1/11/1998
123456 2/5/2004

My desired result would be to get 11/3/1990 associated with the plan code in Table 1. It seems like an easy thing to do, but I am stumped. Thanks for any help.

Seti

 

[bat17]

Try the following SQL in a query. You may want to alter it to "<=" to catch dates that match the table 1 date rather than the previous one.

SELECT table2.PlanCode, Max(Table2.Date) AS MaxOfDate
FROM Sheet1
WHERE (((Table2.Date)<(SELECT Table1.Date FROM table1 WHERE (((Table1.PlanCode)=Table2.PlanCode)))))
GROUP BY table2.PlanCode;

HTH

Peter

 

[Seti]

Thanks for the help. Worked great.