Find Nearest Value
Posted on 2004-09-27
I have two tables:
(field names) UlDate UlPrice
9/25/2004 9:55:01am 16.50
(field names) OptDate OptPrice
9/25/2004 9:55:34am 35.50
I would like to find the nearest UlDate in Table 1 that is nearest to the OptDate in each row of Table 2 then I would like to extract the UlPrice in Table 1 for that nearest date entry and display it in Query 1. It would look something like this:
(field names) OptDate OptPrice UlPrice
9/25/2004 9:55:34am 35.50 16.50
I believe that there is a relatively easy way of doing this through a simple query expression function. Please note there may be NO exact match and I will need to control whether I want to match for a value(date) greater or smaller. I would like to avoid writing a macro and simply do this through a query expression. Table 1 has about 1M rows, Table 2 has about 1M rows as well.
I would appreciate any suggestions for the query function expression. Thanks.