Solved

Find Nearest Value

Posted on 2004-09-27
7
1,133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

734 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