Title
Lookups within a range of a repetitive fields
Post
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?
Repetitive Fields are VERY old technology, pre-Version 3 before there were relationships between files. I seem to remember though that searches were hampered by being tied to first repetition only. I started using with version 3 and I have only used Parent Child record relationships. I think you have hit the wall that is inherent with repetitive fields.