Get Values from a domain table for codes in a second table over - Microsoft Community

hi, trying following , hope can help:

here setup: 

i have access db has tables contains codes fields. example, tablea has "pore_root_quantity_class" field(column) , "pore_root_size" field, both numerical code classes. columns this..

notice codes numerical , duplicate field field. because...

a "metadatadomain" table has values codes, values pulled 2 fields.. choice id field, corresponds numbered code above, , second domain_id field. 

the domain_id field second numerical value specific each field (i.e. quantity_class, pore_root_size)  in third domain table...

what trying run query convert codes in tablea "choice_label" class in second table above, table in end have values both fields instead of codes. table above, "pore_root_size" field have values "fine", "coarse", , "very fine" in first 3 rows 1,7, , 9 correspond values under domain 1281 in second table above.

i know long way run individual queries on each field in table (and other similar tables in db). (1) run query first create unique id field in "metadatadomain" table combines domain id  and choice id fields (i.e. 1281 1, etc), (2) run second query create new unique id (i.e. "1281 1", 1281 7, etc) each field in table a, (3) run third gets value. problem have repeat third step each , every field in table, resulting in multiple queries. @ end have rejoin queries fields 1 table. 

i know long-winded, sorry. know setup of database may not optimal (maybe is, don't know). either way, have no control on that, trying extract data spreadsheet without codes is.

any appreciated.

thanks,

brian

Office / Access / Other/unknown / Office 2007