Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need a Vlookup "true" to match nearest value, but in Access

Status
Not open for further replies.
verizonrep

verizonrep

Programmer
Oct 29, 2002
48
US
I am working on a project where I need to do a Vlookup-type function (true, not false-type) in Access rather than Excel. I have a value in one table that needs to be checked against a list and when it passes one level, it moves to the next one.

Something like this.

Lookup_Table
Level Value
10 1
15 2
20 3


If I put in a different table a value it 5, I need it to be able to reference the table above and return a 1. If it moves to 10.1, it will return a value of 2. Once it passes the 15 level, it moves to 3. This is basically like doing a vlookup in Excel with a "true" range condition.

Can this can be done in a query or a table?

Anyone got an idea???

 
Sort by date Sort by votes
So are you saying everything in the range of 0-14 should return a 1, everything from 15-19 returns a 2 and everything that is 20 or greater will be a 3?


Thanks,

Steve





 
Upvote 0 Downvote
More like:

0-10 returns a 0 (probably need to show that in the table)
10.000000001 - 15 returns a 1
15.000000001 - 20 returns a 2
anything higher than 20 returns a 3

Basically, the moment it passes a level, it moves on to the next one (hence the .000000001 after some of the numbers).

 
Upvote 0 Downvote
It can be done in a query.

Suppose you want the result for an input level of 16.

Then this should work:

SELECT T1.Value
FROM Lookup_Table T1
WHERE T1.Level =
(
SELECT MAX(Level)
FROM Lookup_Table T2
WHERE T2.Level <= 16
)

What we are doing in the inner SELECT is finding the highest Level not exceeding 16 and feeding that result to the outer SELECT.


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
959
renigar
renigar
renigar
  • Locked
  • Question
Access VBA Syntax Error 1
Replies
5
Views
4K
Andrzejek
Andrzejek
atadbitslow
  • Locked
  • Question
how to search data and display matches that include the user's input, but not necessarily match
Replies
5
Views
617
atadbitslow
atadbitslow
swicor
  • Locked
  • Question
Exact match using access upper and lower case
Replies
0
Views
1K
swicor
swicor
TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
4K
TheresAlwaysAWay
TheresAlwaysAWay

Part and Inventory Search

Sponsor

Back
Top