Solved

Numerical Lookup

Posted on 2001-09-05
37
409 Views
Last Modified: 2007-09-26
I have a number in a table and I want to lookup to a list of numbers in another table and find the number closest to mine.

For Example
Table One/Record One = 26

Table Two has a list of numbers 20,30, 40, 50
So I would want to return the number 30 from table 2. Any ideas - I would prefer a query.

Thanks,
Sara :)
0
Comment
Question by:sara1
  • 11
  • 9
  • 4
  • +5
37 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6458349
do you ALWAYS want it to be the higher number?

for example, would 30 ALWAYS return 40 and not 20?

if so, use something like:

SELECT a.Field1, (select MIN( b.field1 ) FROM SecondTable as b WHERE b.field1 >= a.field1) AS Expr1
FROM FirstTable as a;

does this make sense?

dovholuk
0
 
LVL 1

Author Comment

by:sara1
ID: 6458406
No, I want which ever number is closest. Not necessarily the highest. You brought up a good point though that if they split the difference, then I could use your suggestion.

Thanks,
Sara
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6458449
so try this on for size... i don't know how FAST it'll work, but it seems to do it's job.

SELECT a.Field1, (select MIN( b.field1 ) FROM Table7 as b WHERE b.field1 >= a.field1) AS Highest, (select MAX( b.field1 ) FROM Table7 as b WHERE b.field1 <= a.field1) AS Lowest, IIf(Abs([Highest]-[Field1])<=Abs([Lowest]-[Field1]),[Highest],[Lowest]) AS Closest
FROM Table8 AS a;

replace Table8 and Table7 (my test tables) with the names of your tables and replace the a.field1 and b.field1 with the appropriate fields and give it a shot... it worked for me!

;)

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6458450
of course, the field you'll wanna use would be the "closest" field.

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6458454
i edited the sql so that it has artifically "high" and "low" values

try:

SELECT a.Field1, nz((select MIN( b.field1 ) FROM Table7 as b WHERE b.field1 >= a.field1),9999999) AS Highest, nz((select MAX( b.field1 ) FROM Table7 as b WHERE b.field1 <= a.field1),-9999999) AS Lowest, IIf(Abs([Highest]-[Field1])<=Abs([Lowest]-[Field1]),[Highest],[Lowest]) AS Closest
FROM Table8 AS a;
0
 
LVL 57
ID: 6458498
If that proves to slow, I have another method that I believe would be faster.

Jim.
0
 
LVL 2

Accepted Solution

by:
oui_li earned 100 total points
ID: 6458758
If You have Acc2000 then try this. If you have 97, you will have to do a seperate query with the sub query.

SELECT
  <SearchValue>,
  FIRST(<LookUpValue>)
FROM
  <SearchTable>
  INNER JOIN
    (
    SELECT
      <SerchValue>,
      <LookUpValue>,
      ABS(<SearchValue> - <LookUpValue>) AS Difference
    FROM
      <SearchTable>,
      <LookUpTable> ***JOINS IF NEEDED***
    ) AS LookUp
  ON <SearchTable>.<SearchValue> = LookUp.<LookUpValue>
ORDER BY
  <SearchTable>.<SearchValue>,
  LookUp.Difference,
  LookUp.<LookUpValue>

This should be quick.
0
 
LVL 1

Author Comment

by:sara1
ID: 6458800
This will be done with a large db so I would prefer the fastest.
0
 
LVL 57
ID: 6459086
oui li's method is along the lines of what I was thinking of.  A DMin() based on a query that generats the difference between the search value and the existing one.  

The query would only need to columns, the value and the difference.

Should be quite fast.

Jim.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6459211
with all seriousness, i'd like to see the difference.  i don't know the inner workings of DMin(), DMax(), DLookup() etc., but i'd bet dollars to donuts it's just a wrapper function for a select statement. the same select statements that i've posted. i would really be interested in seeing a comparison.

personally, i tend to shy away from dmin() etc. calls. at least with a97, when you move to a sql server the same syntax you use in your previous queries generally doesn't work in stored procedures...

just my opinion. let us know what's faster! there's NO DOUBT that DMin() is "easier" on the eyes. lol.

dovholuk
0
 
LVL 3

Expert Comment

by:carruina
ID: 6460236
This query search the closed value to 20

SELECT Min(Abs(20-[VALUE])) AS Expr1, Tabla1.VALUE
FROM Tabla1
GROUP BY Tabla1.VALUE
ORDER BY Min(Abs(20-[VALUE]));


And the first record is the closest
0
 
LVL 57
ID: 6460395
dovholuk:

  Yes, I'd like to really see the difference as well.  The Domain aggragate functions are a wrapper for an SQL statement, but it appears that there has been some optimization done.

  Over the years, it's always been stated in Access circles that "Domain functions are always slower" then other methods.  About 4 years ago, this was proven to be false with some actual testing.  Depending on what is being done, the Domain functions can actually be quite a bit faster.  So I generally suggest trying both (Domain vs VBA/SQL).

  However when I said, "I think this might be faster", I was comparing the fact that your doing a total of 3 selects: one for the highest using Max(), one for the lowest using Max() again, and the overall select, vs. doing basically 2 selects: one that builds the difference (no domain function, just straight math), and then the DMin() to get the value.  So it's one Domain function vs two and fewer selects.  That's why I thought it would be faster.  Note that this could  be done with SQL as well as it really is a different way of looking at the problem.  Also, your using a saved query, which will already be costed, so you'd save some overhead there as well.

carruina:
  I thought of this method as well, which I believe would be the best, but I don't think you can always count on the fact that their is some value in the table with X-Y>0 range.  Given that, the query has the chance to return nothing.

Jim.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6460428
ahah. now i see what you're talking about. i tried to provide the high and low value without thought (for demonstration). thanks for qualifying for me what you had in mind. ;)

dovholuk
0
 
LVL 3

Expert Comment

by:carruina
ID: 6460485
JDettman:

I don't understand what is the problem.
If you use the ABS function the query calculate the "distance" between two numbers independent of the positive/negative sign.

0
 
LVL 18

Expert Comment

by:deighton
ID: 6460487
select top 1, value2  from ( select  table2.value2,abs(table2.value2 - (select top 1 table1.value1 from table1)) as expr from table2) order by expr


where table1 contains the single test value (field = value1)

table2 contains range of vALUES (field = value2)

then the above sql gives the closest value in table 2

0
 
LVL 3

Expert Comment

by:carruina
ID: 6460495
JDettman:

I don't understand what is the problem.
If you use the ABS function the query calculate the "distance" between two numbers independent of the positive/negative sign.

0
 
LVL 57
ID: 6460543
"This query search the closed value to 20"

  It pays to read the response.  I looked at this totally wrong. Sorry.

Jim.
0
 
LVL 18

Expert Comment

by:deighton
ID: 6460572
I'm not sure if you have multiple values in table 1 to query - but either way you could use

SELECT t1.value1 AS table1val, (select  top 1 t2.value2 from table2 t2 order by  abs(t2.value2 - t1.value1)) AS closest
FROM table1 AS t1;
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:sara1
ID: 6461332
Wow, thanks for all of the ideas. Going to take me a little while to apply my criteria and pick the one that works best for me.

Sara :)
0
 
LVL 1

Author Comment

by:sara1
ID: 6461866
I'm confused. Everyone seems to following the same logic so I must be missing something.How I am generating a difference between the values in the two tables. What I am linking on? I need to look at each record in table two to find the closest.

Sara :)
0
 
LVL 1

Author Comment

by:sara1
ID: 6461876
I should mention that table one contains more than a single record also.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6461880
sara,

try my method first. it will show you the record, the closest one higher, the closest one lower, and the closest overall...

then maybe it'll click about what JDettman was talking about...

if not, let us know and someone will surely help you out.

dovholuk
0
 
LVL 1

Author Comment

by:sara1
ID: 6462043
OK scratch my earlier comment. I have it to the point where I get a list of the values and the differences and I want to pick the first one. However when I choose first it doesn't give me the first one.

SELECT [Z-TEST].Object_ID, First([Z-TEST].Test_Workspace_Standard) AS FirstTest_Workspace_Standard, First([Z-TEST].Test_Standard) AS FirstOfTest_Standard, First([Z-TEST].ASF) AS FirstOfASF, First([Z-TEST].Test_Applied_Standard) AS FirstOfTest_Applied_Standard, First([Z-TEST].Difference) AS FirstOfDifference
FROM [Z-TEST]
GROUP BY [Z-TEST].Object_ID
ORDER BY [Z-TEST].Object_ID, First([Z-TEST].Difference);

Dovholuk, when I try your explanation it doesn't recognize the field name from the search table

Sara :)
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6462347
can you post the table names and fields?

thanks

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6462359
here's my guess at your tables and fields...

SELECT a.[Z-TEST], nz((select MIN( b.[Z-TEST] ) FROM Test_Workspace_Standard as b WHERE b.[Z-TEST] >= a.[Z-TEST]),9999999) AS Highest, nz((select MAX( b.[Z-TEST] ) FROM Test_Workspace_Standard as b WHERE b.[Z-TEST] <= a.[Z-TEST]),-9999999) AS Lowest, IIf(Abs([Highest]-[Z-TEST])<=Abs([Lowest]-[Z-TEST]),[Highest],[Lowest]) AS Closest
FROM Test_Standard AS a;


dovholuk
0
 
LVL 18

Expert Comment

by:deighton
ID: 6463358
sara,

you really need to post the table names & field names - obviously if we guess what the tables/fields are called, then we will give you SQL which will be more difficult for you to follow.

..if you had done this then you would have sql that you could try and would work with your setup.
0
 
LVL 1

Author Comment

by:sara1
ID: 6464550
Thanks everyone - I am so close, I just need someone to explain why the first is not working. For example:

ObjectID    Test_Standard  ASF  Difference
AA1234            A         15     2
AA1234            A         26     6
AA1255            B         35     2
AA1255            C         46

Now if I group the objectID and use first for the rest - shouldn't the first record be returned. This
0
 
LVL 1

Author Comment

by:sara1
ID: 6464551
Thanks everyone - I am so close, I just need someone to explain why the first is not working. For example:

ObjectID    Test_Standard  ASF  Difference
AA1234            A         15     2
AA1234            A         26     6
AA1255            B         35     2
AA1255            C         46

Now if I group the objectID and use first for the rest - shouldn't the first record be returned. This
0
 
LVL 1

Author Comment

by:sara1
ID: 6464562
I don't know how that got submitted - I think I hit something on the keyboard. Any way to finish my question

Thanks everyone - I am so close, I just need someone to explain why the first is not working. For example:

ObjectID    Test_Standard  ASF  Difference
AA1234            A         15     2
AA1234            A         26     6
AA1255            B         35     2
AA1255            C         46     4  

Now if I group the objectID and use first for the rest - shouldn't the first record for each object id be returned? This is not what is happening.

Sara :)
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6464667
When I put the above table in and build the query as you suggest:  Groupby on ObjectID and First on all other fields, I get 2 records:
AA1234            A         15     2
AA1255            B         35     2


Is this correct?
0
 
LVL 1

Author Comment

by:sara1
ID: 6464675
This is correct, but this is not what I'm getting.
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6464718
You could probably write a function that compares the numbers and scrolls through all the records.  This is just off the top of my head, but it would look something like:

Dim YourNumber As Long
Dim DiffAmount As long
Dim ClosestNumber As Long
Dim dbs as Database
Dim rst As Recordset

set dbs = CurrentDB
Set rst = dbs.OpenRecordset("SELECT NumberField FROM TableName")'Use select Distinct if you do not want duplicates

YourNumber = Me![ControlName]'Change this to whatever you
'are using to pass the variable to the function
rst.MoveFirst
'Compare numbers for starting point
If YourNumber > rst![NumberField] Then
    DiffAmount = YourNumber - rst![NumberField]
Else
    DiffAmount = rst![NumberField] - YourNumber
End If
ClosestNumber = DiffAmount
rst.MoveNext
'Compare next numbers
Do Until rst.EOF
If YourNumber > rst![NumberField] Then
    DiffAmount = YourNumber - rst![NumberField]
Else
    DiffAmount = rst![NumberField] - YourNumber
End If
If DiffAmount < ClosestNumber Then
 ClosestNumber = DiffAmount
End If
rst.MoveNext
Loop
msgbox"The closest Number is " & ClosestNumber _
   & ">",vbOkOnly



0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6464724
P.S. - make sure you clos rst and set rst and dbs  = Nothing.
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6464882
I was giving this some more thought and realized a serious flaw in my code.  Change the Do Until portion to:

ClosestNumber = rst![NumberField]
rst.MoveNext
'Compare next numbers
Do Until rst.EOF
If YourNumber > rst![NumberField] Then
   DiffAmount2 = YourNumber - rst![NumberField]
Else
   DiffAmount2 = rst![NumberField] - YourNumber
End If
If DiffAmount1 > DiffAmount2 Then
ClosestNumber = rst![NumberField]
DiffAmount 1 = DiffAmount2
End If
rst.MoveNext
Loop
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6464901
Sara,

Might wanna check your final query.  All I did was build a table like you had listed and ran this query:

SELECT Table3.ObjectID, First(Table3.Test_Standard) AS FirstOfTest_Standard, First(Table3.ASF) AS FirstOfASF, First(Table3.Difference) AS FirstOfDifference
FROM Table3
GROUP BY Table3.ObjectID
ORDER BY Table3.ObjectID, First(Table3.Test_Standard), First(Table3.ASF), First(Table3.Difference);


dill
0
 
LVL 1

Author Comment

by:sara1
ID: 6465096
Yippee, I got it work. For some reason my query would not worked when it was based on a query, but when I made a table the first thing worked.

I appreciate everyone's help on this. I am awarding the points to oui li since he/she seems to be the first to propose the solution that I could work with. I hope no one is offended - I really gave it shot with all of the suggestions.

Sara :)
0
 
LVL 2

Expert Comment

by:oui_li
ID: 6662480
Sara,
Thanks for the points. Glad to help... (even though I did not participate in the argument much).
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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

19 Experts available now in Live!

Get 1:1 Help Now