Nearest Date (1 Viewer)

[KirkComer]

I need to find the nearest dates between two tables.

Table1 contains dates 11/1/2006 to 11/30/2006

Table2 contains only two dates:
11/5/2006
11/19/2006

I want Query1 to produce the nearest date in Table2 beside each of the dates in Table 1.
Example:
Date Nearest Date
11/1/2006 11/5/2006
11/13/2006 11/19/2006
etc.

 

[KeithG]

first make a query using the datediff function to calculate the days between the dates in the two tables. Then make another query based on the query you just made and group it by NearestDate and the minuim of the calculations of days between.

 

[KirkComer]

KeithG,

Thanks for the quick response!

I don't know if I followed you exactly but I was able to get very close to my final result. The only problem I get now is a duplicate on 11/12 because its the same number of days between 11/5 and 11/19. I don't want any duplicates and I would prefer for the date to round down (in this case 11/5).

I have attached the updated database for all to look at and possibly help me to find a solution.

 

[KirkComer]

OK. I figured it out. I simply grouped my last query by date, by difference and used the "Min" function in the total row to get my answer.

Now I have to broaden my date range consideribly. If I have any problems I'll be back on here begging for help again...