I’d like to conduct a lookup to group items into classes based on numeric values. I have a table that already does this. It’s called TextileWeightKey and it looks like this:
In case the picture doesn’t load, the important columns in this key table are WeightCode (the primary key), MinWeight, and MaxWeight. The first record has the values L1, 0, and 33.9 respectively. The second record has the values L2, 33.9 and 67.8, and so on. These represent different classes of textile weights. All of the L1 class textiles will weigh between 0 and 33.9 grams per square meter (gsm), all of the L2 class textiles will weight between 33.9 and 67.8 gsm, and so on.
In another table, Textiles, I have a records representing many unique textiles. Each individual textile has a specific weight, such as 17.4 gsm and there is a field in this table (TextileWeight) that receives this value from those doing the data entry.
I would like to have a lookup field in the Textiles table called TextileWeightCode that displays the weight code for that class of the textile. To do this, I’d like to lookup the actual textile weight (17.4 in this example), compare it with the MinWeight (and/or MaxWeight) field of the TextileWeightKey table, and return the value of the WeightCode field (L1 in this example).
I believe one reason I am unable to do this currently is that field I am conducting my lookup search in (MinWeight) is not the primary key of TextileWeightKey. But, to thest this, I have made a duplicate table in which the primary key IS the mininum weight field and it still doesn’t work.
The problem is that when I try to create a lookup field in the Textiles table, it won’t even let me select the TextileWeight field (or any other field that contains numeric values) as the reference field whose values will be looked up in the TextileWeightKey table (no matter how that table is structured).
Is there any way to do what I want to do?