Continuous Range Lookup from Discrete Table

  • Thread starter Thread starter Krishna Khemraj
  • Start date Start date
K

Krishna Khemraj

I have a field that can contain any value from 1 to 10
million and need to categorize it using another table
that contains values such as 0k 5k 10k 15k 20k 25k 50k
75k 100k 250k 500k 1000k 1500k 2000k 5000k 10 million
(with a few others thrown in for good measure.) How can
I lookup a value such as 1,256,434.57 and return 1000k or
12,567.67 and return 10k?

For consistency please refer to LargeTbl and LookupTbl as
the two tables referred to above.

Winxp Accessxp
Thanks in advance.
 
I forgot to mention this is in qryTestQry and the query
contains two tables LargeTbl and LookupTbl.
 
Dear ???:

You appear to want the largest category from LookupTbl whose value is
less than or equal to the value in LargeTbl.

I'll assume that LookupTbl has two columns: CategoryLabel and
CategoryValue, like this:

CategoryLabel CategoryValue
0k 0
5k 5000
15k 15000
20k 20000

and so forth.

To make this work, I'd do this:

SELECT *,
(SELECT MAX(CategoryValue) FROM LookupTbl T1
WHERE T1.CategoryValue <= T.Value) AS CategoryValue
FROM LargeTbl T

You can save this query, then join its results with LookupTbl to read
the CategoryLabel.

If your particulars are quite different from this then please provide
more information.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top