Lookup value between two numbers in query (1 Viewer)

[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?

 

[Pat Hartman]

Your data makes no sense and so I improvised. This is the query you need. I added the two values (1120, and 2050) to the value table and the query returns two rows. Row 2 and row 6 NOT row 8.
2050------ is NOT between minimum_Value 2401
and 2600

SELECT Categ_Tb.category_Id, Categ_Tb.minimum_Value, Categ_Tb.Maximum_Value, Value_Tb.Value
FROM Categ_Tb INNER JOIN Value_Tb ON Categ_Tb.minimum_Value <= Value_Tb.Value and Categ_Tb.maximum_Value >= Value_Tb.Value;

This is called a non-equi-join and it cannot be represented by the QBE in design view. You can only view the query in SQL view.

Because there is no join field, Access makes a Cartesian Product and then uses criteria to whittle down the results. This query will probably be more efficient than the domain function but it is still very costly if one or both of the tables is large. The Cartesian Product multiplies tblA times tblB so if tblA contains 200,000 rows and tblB contains 20 rows, the resultset in memory will be 200,000 * 20 or 4 million rows. But the domain function requires running 200,000 queries.