Ms-access – Access Value between ranges

ms-access-2010

I'd like the following population logic built in an Access database:

I have two tables. The first, Table1 contains a set of buckets, with a starting and ending value:

Start   End   Bucket        
0.00    0.08    1           
0.08    0.16    2           
0.16    0.25    3           
0.25    0.33    4           
0.33    0.41    5           
0.41    0.49    6           
0.49    0.58    7           
0.58    0.66    8           
0.66    0.74    9           
0.74    0.82    10          
0.82    0.90    11          
0.90    0.99    12          
0.99    1.23    13          
1.23    1.48    14          
1.48    1.73    15          
1.73    1.97    16          
1.97    2.22    17          
2.22    2.47    18          
2.47    2.71    19          
2.71    2.96    20          
2.96    5.00    21          
5.00    10.00   22          
10.00   25.00   23          
25.00   101.00  24

Table2 contains values that I need assigned to a bucket by matching the My Values column to lie between the Start and End range in Table1:

Table2 where Bucket has been populated with the corresponding Bucket value from Table1:

My values           Bucket 
7.416438356         22
7.416438356         22
7.416438356         22
7.416438356         22
0.252054795         4
0.750684932         10
0.504109589         7
0.504109589         7
0.504109589         7
9.690410959         22
0.504109589         7
0.504109589         7
0.706849315         9
0.504109589         7
1.504109589         15
0.567123288         7
0.504109589         7
0.753424658         10
85.56164384         24
1.482191781         15
2.994520548         21
3.594520548         21
99                  24
0.504109589         7

I can populate Bucket for My values column in Excel easily via the VLookup function, but want to be able to do the same using Microsoft Access.

The only solution I can think of is a lot of IIF() functions, but that's messy and not classy.

Best Answer

Try something along the lines of:

UPDATE Table2 
INNER JOIN Table1 ON Table2.[My Values] >= Table1.Start AND Table2.[My Values] < Table1.End
SET Table2.Bucket = Table1.Bucket;

Essentially, we get the value from Table1 by comparing the My Values field in Table2 to see if it is between Table1.Start and Table1.End

This will update the entire Table2 in a single statement, and shows the power of set-based approaches to data vs what is referred to as "row by agonizing row" or RBAR. See https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/ for a great explanation of that.

Related Question