Lookup value between two numbers in query

[hatmak]

hi

I have table Categ_Tb ---> category_Id - minimum_Value - Maximum_Value

AND Other table Value_Tb

Value

i need query Query1

LOOKUP this VALUE in minimum_Value - Maximum_Value

if it between minimum_Value - Maximum_Value return category_Id

 

[mike60smart]

hi

I have table Categ_Tb ---> category_Id - minimum_Value - Maximum_Value

AND Other table Value_Tb

Value

i need query Query1

LOOKUP this VALUE in minimum_Value - Maximum_Value

if it between minimum_Value - Maximum_Value return category_Id

Hi

Not really understanding your request.

Your table of Values are 500,600,800 & 900

Then your Minimum and Maximum Values are all Greater than 1000

Can you give us an example of what you expect to see as the output of your Query ?

 

[hatmak]

Hi

Not really understanding your request.

Your table of Values are 500,600,800 & 900

Then your Minimum and Maximum Values are all Greater than 1000

Can you give us an example of what you expect to see as the output of your Query ?

please ignore value 500,600,800,900


use this value

Value
1120​
2050​
1799​
1650​

1120 ------ is between minimum_Value 1101 and
Maximum_Value 1200

so it return category_Id 2


2050------ is between minimum_Value 2401
and 2600
so it return category_Id 8

 

[bob fitz]

You could use:
SELECT Value_Tb.Value, DLookUp("category_id","Categ_Tb","minimum_Value <= " & [Value] & " AND maximum_Value >= " & [Value]) AS CatID
FROM Value_Tb;
but DLookup() is said to be slow with large datasets.

 

[hatmak]

You could use:
SELECT Value_Tb.Value, DLookUp("category_id","Categ_Tb","minimum_Value <= " & [Value] & " AND maximum_Value >= " & [Value]) AS CatID
FROM Value_Tb;
but DLookup() is said to be slow with large datasets.

many thanks bob fitz

 

[hatmak]

but DLookup() is said to be so with large datasets.


what can use if large database

 

[bob fitz]

but DLookup() is said to be so with large datasets.


what can use if large database

I'm not sure you have any other alternatives.

 

[hatmak]

I'm not sure you have any other alternatives.

no other alternative

many thanks again for your help

 

[bob fitz]

no other alternative
many thanks again for your help

Sorry, I'm not saying that there is no alternative, just that I don't of any without a relationship between the tables.
There are people with far more knowledge than I have that visit this forum. Don't give up hope on hearing from someone else
I have no actual evidence of it's slowness, other than that is what I've read.
Have you tried it out to see how long it takes?