AnsweredAssumed Answered

Lookups within a range of a repetitive fields

Question asked by miw on May 16, 2015
Latest reply on May 21, 2015 by philmodjunk


Lookups within a range of a repetitive fields


I have two tables:

Type and Item

Type has the fields T_Year, T_Grade and T_Value.  For both Grade and Value fields, there are 10 repetitions; say that the data in these two repetitive fields for T_Year "2015" are as follows:

T_Grade: A; B; C; D; E; F; G; H; I; J

T_Value: 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 

Item also has three fields I_Year, I_Grade and I_Value.  I want an Item record to lookup a value (I_Value) in the Type table for an input I_Year and I_Grade.  So if I have an Item with I_Year "2015" and a I_Grade of "D", the I_Value lookup from the Type table would then be "4".

I created the relationship between the Type and Item table as follows:

T_Year >---- = ----< I_Year and

T_Grade >---- = ----< I_Grade

I was hoping that the I-Value would then be looked up among the repetitions within T_Grade (in this case it would be the 4th repetition for the I_Grade value of "D") and lead to the equivalent T_Value repetition (the 4th repetition in T_Value being "4" in this example).  However, when there is a match on the Year field, only the first Type repetition for T_Value is given.  Is there a way to due such a lookup within repetitive tables?