[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 430

# Numerical Lookup

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
sara1
• 11
• 9
• 4
• +5
1 Solution

Commented:
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

Author Commented:
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

Commented:
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

Commented:
of course, the field you'll wanna use would be the "closest" field.

dovholuk
0

Commented:
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

PresidentCommented:
If that proves to slow, I have another method that I believe would be faster.

Jim.
0

Commented:
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

Author Commented:
This will be done with a large db so I would prefer the fastest.
0

PresidentCommented:
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

Commented:
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

Commented:
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

PresidentCommented:
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

Commented:
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

Commented:
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

Commented:
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

Commented:
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

PresidentCommented:
"This query search the closed value to 20"

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

Jim.
0

Commented:
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

Author Commented:
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

Author Commented:
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

Author Commented:
I should mention that table one contains more than a single record also.
0

Commented:
sara,

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

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

dovholuk
0

Author Commented:
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

Commented:
can you post the table names and fields?

thanks

dovholuk
0

Commented:
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

Commented:
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

Author Commented:
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

Author Commented:
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

Author Commented:
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

Commented:
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

Author Commented:
This is correct, but this is not what I'm getting.
0

Commented:
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

Commented:
P.S. - make sure you clos rst and set rst and dbs  = Nothing.
0

Commented:
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

Commented:
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

Author Commented:
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

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

## Featured Post

• 11
• 9
• 4
• +5
Tackle projects and never again get stuck behind a technical roadblock.