Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19

    Finding Closest Value In Another Table

    Hello,



    I such a way to update field with a closest value from another table.

    Example:

    I have two tables:

    table 1:
    Name= "Word"
    Value= 60
    Number_of_nearest_ID = this field would be updated

    table 2:
    ID:125
    Name= "Word"
    Value= 62


    ID:126
    Name= "Word"
    Value= 59


    ID:127
    Name= "Word"
    Value= 63

    I want to update the field Number_of_nearest_ID in table1 with value 126, becouse closest number to 60 is 59, and number 59 have ID:126.
    I try with SQL, but I can't use aggregation function in WHERE and it is impossible for me.
    Please help me

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In a query Q1, join the 2 Tables on WORD,
    Make a field to take the difference ,
    Word, Diff: ABS(tbl1.value - tbl2.value) , ID

    make Q2 , use Q1 to get the MIN of Diff. Append to a tableTemp.
    then update your master table with tblTemp.

  3. #3
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    Its doesnt work, becouse I become minus numbers too.
    And MIN() tell me everytime - minus as result but maybe on + scala is a value closest to zero.

    -3
    0 my number
    2

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    take absolute value
    ABS(A-B)

  5. #5
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    if I take ABS(), in the second QUERY i can't use MIN() becouse I become error, that its imposibble to use MIN() with agreggation function(abs)
    thats the problem

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Attach/Show us a sample database.

    If you are actually looking for similarity in text strings, you may want to research Levenshtein distance.

  7. #7
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    I can use only MS Access.
    Is attach/show in MS available?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Orange helped me with this long ago. IMO you will have to use a subquery - provided that you have a rule that it is the closest value that is less than or the closest value that is greater than. If the numbers were 58, 60 and 62, which would you choose; 58 or 62 if you don't apply such a rule?
    Last edited by Micron; 01-14-2022 at 09:30 AM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    pio92,

    I think you can attach a copy of your database, or a test data base to give readers some data to work with.

    See How to Attach Files link at the top of the page.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    just curious - what if you had a table2 record with 59? both 59 and 61 would return a different value of 1 - so which would you choose?

    Edit: just seen Micron has asked the same question...

  11. #11
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    Hi ,
    It doesn't matter.
    When my number is for example 60, then it doesn't matter of the result is 58 or 62.
    One of them.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This thread might help you figure it out.
    https://www.accessforums.net/showthr...highlight=Date

    also, about subqueries
    http://allenbrowne.com/subquery-01.html

    What I think you're needing is to get the next closest value (B) that is either greater than value (A) OR the next closest that is less than - but search for one or the other. Not sure you can do both. The values you are going to look for need to be ordered, either ascending or descending, depending on whether you're going to look for > or <. That means if looking for next closest to 58 that is greater and that value is 65, That's what you'll get. However, if 57 is in that table, you won't get it. If that's not a problem, then visit those links to see how subquery might work for you. If that would be a problem, perhaps post 6 here will help. Otherwise, I suppose you'd need 2 queries - one for > and one for < and maybe a third query with some type of expression that will flag that 65-58 is a larger number than 58-57, so choose the record with 57. IMO, unique id fields will have to be in both tables for it to even have a chance to work.

    I agree that a zipped db copy would help, but I'm not sure we're clear on the requirements yet.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @pio92

    Please clarify what exactly you are comparing.

    Are you looking for similarity in strings/words?
    eg, squint vs quinte
    bare vs bear
    cent vs scent

    Your "word" example is confusing me.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Edited after testing the aircode

    first find the minimum value - query Q1

    Code:
    SELECT table1.name, table1.value, min(abs(table1.value-table2.value)) as minnum
    FROM table1 inner join table2 on table1.name=table2.name)
    GROUP by Table1.name, table1.value
    next join this to table2 - query Q2

    Code:
    SELECT  table2.name, Q1.minnum, min(table2.ID) as minID
    FROM table2 INNER JOIN Q1 ON table2.name=Q1.name AND table2.value=Q1.minnum
    WHERE (((Table2.value) Between [q1].[value]-[minnum] And [q1].[value]+[minnum]))
    GROUP BY table2.name
    finally update table1

    Code:
    UPDATE Table1 SET Number_of_nearest_ID=DLookUp("minID","Q2","Name='" & [Table1].[name] & "'");
    Note I really hope your example naming is just for convenience. If you are actually using them then be aware Name and Value are reserved words and should not be used for field names (e.g. fld.name will return the name of a field, fld.value will return it's value) - you will get weird errors at some point

  15. #15
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    Thanks,
    I have now only one problem with update.

    If I use:
    Code:
    UPDATE Table1 SET Number_of_nearest_ID=DLookUp("minID","Q2","Name='" & [Table1].[name] & "'"); 
    field Number_of_nearest_ID ist't updated is empty


    If I use:
    Code:
    UPDATE Table1 INNER JOIN Q2 ON Table1.name Q2.name
    SET Table1
    .Number_of_nearest_ID Q2.minID 

    I get error:
    "
    operation must an updateable query access"

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update table based on closest match on date
    By dalahans in forum Queries
    Replies: 6
    Last Post: 06-07-2018, 07:50 AM
  2. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  3. Replies: 2
    Last Post: 08-01-2017, 01:40 AM
  4. Replies: 2
    Last Post: 09-03-2015, 04:38 PM
  5. Trouble finding closest match
    By cutsygurl in forum SQL Server
    Replies: 1
    Last Post: 02-22-2013, 03:59 PM

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