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?