Results 1 to 3 of 3
  1. #1
    moss567 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    2

    Closest value above and closest value below a specific value in a separate table

    Hello,




    I am having issue getting the closest value above and closest value below in table relative to another singular value in a separate table. The values also have correlating ID numbers that must be referenced.

    Can someone please help?

    I think this might be getting close DMin(IIf([survey]![depth]>[structure]![depth_to]),[survey]![depth],0),[structure]![depth_to],[structure]![hole_id]=[survey]![hole_id]) but it doesn't work.

    Thank you,

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    thats not how DMIN works:
    DMIN ( "[field to return]", "table/query", "where clause")

    what do you mean 'closest' value? next index? , 1 less that the current [depth]?

  3. #3
    moss567 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    thats not how DMIN works:
    DMIN ( "[field to return]", "table/query", "where clause")

    what do you mean 'closest' value? next index? , 1 less that the current [depth]?

    So table 1 has
    ID_ Depth
    1____12.5
    1____16.7
    2____12.4
    2____18


    Table 2 has
    ID _Depth
    1 ___ 5
    1 ___ 10
    1 ___ 15
    1 ___ 20
    2 ___ 6
    2 ___ 11
    2 ___ 16
    2 ___ 21


    So I want Table 1 to have 2 more columns from table 2 values, eg.
    ID_ Depth _ Closest value above _Closest value below
    1 __12.5 ____15 _______________10
    1 __16.7 ____20 _______________15
    2 __12.4 ____16 _______________11
    2 __18 ______21 ______________16


    I just need to get the formula for these 2 columns because there is a lot of data and doing manually will be painful and subject to human error.


    Thanks,
    Michelle

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to join records with the closest values
    By dipique in forum Queries
    Replies: 2
    Last Post: 09-08-2014, 04:11 PM
  2. Replies: 4
    Last Post: 05-28-2014, 10:09 AM
  3. Find Variables closest to specified Date
    By crimedog in forum Reports
    Replies: 1
    Last Post: 01-30-2014, 11:10 AM
  4. Trouble finding closest match
    By cutsygurl in forum SQL Server
    Replies: 1
    Last Post: 02-22-2013, 03:59 PM
  5. find the closest year
    By 12345678 in forum Queries
    Replies: 1
    Last Post: 05-25-2011, 05:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums