Solved

Find Nearest Value

Posted on 2004-09-27
7
1,132 Views
Last Modified: 2012-06-21
I have two tables:

Table 1
(field names)    UlDate                         UlPrice
                       9/25/2004 9:55:01am   16.50
etc

Table 2
(field names)    OptDate                         OptPrice
                       9/25/2004 9:55:34am     35.50
etc..

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:

Query 1
(field names)    OptDate                         OptPrice         UlPrice
                       9/25/2004 9:55:34am     35.50             16.50
etc..


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.
0
Comment
Question by:adorenbaum
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 12162980
Hello adorenbaum,

Are you saying that the only field involved in the match is the datetime field?
(NONE of the other fields matter)

Pete
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12163043
Try this

SELECT OptDate, OptPrice, UlPrice FROM Table1 INNER JOIN Table2 ON (abs(datediff ("s", Table1.UlDate, Table2.OptDate)) < 60)


You can change 60 to whatever the limit you want
where 60 is in second
0
 

Author Comment

by:adorenbaum
ID: 12163791
peter57r:

Yes.  Please note this is NOT AN EXACT MATCH, but find the nearest value (greater or lesser as specified by me).
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:adorenbaum
ID: 12163844
EDDYKT:

Thanks for your comment.  Two questionsL

1.  How do I change your syntax if I want to find the nearest GREATER value vs. find the nearest LESSER value?

2.  Can I enter your code directly into a query builder in design view or will I need to access this through a macro.  (I'm familiar with VB but I would like to avoid writing a full macro.  Instead, I would like to simply enter a function expression into the design view query builder).

Thanks.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12164292
select first max(a.uldate), a.ulprice, b.optdate, b.optprice from table1 as a, table2 as b
where a.uldate < b.optdate;

Closest uldate that is less than optdate

select first min(a.uldate), a.ulprice, b.optdate, b.optprice from table1 as a, table2 as b
where a.uldate > b.optdate;

Closest uldate that is more than optdate
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 12332390
This should do in one go:

  SELECT
    tblTable2.*,
    (SELECT TOP 1
      T.UlDate
    FROM
      tblTable1 AS T
    GROUP BY
      T.UlDate
    ORDER BY
      Min(Abs(DateDiff("d",[tblTable2]![OptDate],T![UlDate])));) AS NearestDate
  FROM
    tblTable2;

/gustav
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 12635366
Did this work for you?

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question