Solved

Find Nearest Value

Posted on 2004-09-27
7
1,136 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 51

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 51

Accepted Solution

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

/gustav
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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