1

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.

  • Is this one or two tables? – ypercubeᵀᴹ Aug 15 '14 at 13:58
  • Edited. Yes, they are two seperate tables – granady Aug 15 '14 at 14:01
1

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.

  • Thanks for the idea. I'll try it out and come back with the results. – granady Aug 15 '14 at 16:49
  • 1
    I used a SELECT - LEFT JOIN to pull in the bucket, but used your join creterias and it worked. thanks for the help. – granady Aug 18 '14 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.