Hello,
I am looking for way to designate a field in a table to query based on the results of another query. Previously I was using the switch function but a change in the table's parameters has made the formula too large to enter.
Basically the first query calculates each subscribers family status up to 5 children and assigns a numeric code (Family code). There are 23 codes. Each of the 23 codes are represented as fields on the table that houses the premiums. The table as a whole contains rates by Client ID, benefits and date spans. What I need is a way for query2 to look at the family code results from query1 and use that to search the rate table where the field name matches the family code. For example, if the query1 result is 200 then query2 would be linked to [Rates]![200], and if the result was 305 it would be linked to [Rates]![305] and so on. I hope that makes sense. Basically the table reference will be static but the field reference will be variable. Again, I previously had this working with the switch function so I know it is possible, I just cannot figure out how to code and I am assuming that the solution will need to be VBA but other options and advice are welcome.
Thanks