Solved

Find Nearest Value

Posted on 2004-09-27
7
1,127 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

947 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

18 Experts available now in Live!

Get 1:1 Help Now