Solved

Find Nearest Value

Posted on 2004-09-27
7
1,123 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 49

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 49

Accepted Solution

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

/gustav
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now