use field value as field name in formula

[vgeng]

I have a table with a field program (eg x, y, z) and several text-fields (eg. a, b and c)
In another table i have a field containing the different program names and in another field is mention a, b OR c

so my tables would be
programs fielda fieldb fieldc
X aa bb cc
Y aaa bbb ccc
Z aaaa bbbb cccc

and
programs fieldx
X a
Y c
z c

How can I build a query-formula that if my programm equals x gives as result aa and if my program equals z results in cccc

thanks for your help

 

[hneal98]

It sounds like you would need a UNION query. But what you wrote is not clear. What do yo need to do with the second table?

 

[cottontop]

Let's lable the two tables:

TableA & TableB

Join the two tables on programs.

Creat a field,
test: iif([tableB]![programs]="X", [tableA]![fieldA], iif([tableB]![programs]="Z", [tableA]![fieldc],null))

I think this is what you're asking!!

 

[vgeng]

thanks

but the problems is that I have many programs, so my IIF-formula is gonna be rather long

the second table is needed to determine which field of the first table is needed to get to my final result

the reason of this all :
I have to match two entries but the common reference isn't always in the same field. Its place depends which program have reated the entry