I have two tables CODES and DATA.
CODES has two fields, codes and descriptions. The code field has the full hierarchy of codes, ie 1 digit, 2 digit, 4 digit, 8 digits. DATA is a very big dataset that I'm trying to link to and so I want to do this in one query, not two
The main data table has only 8 digit codes in it (so linking the two tables and getting the description for them is no problem). I use the left function in the query to get the two first digits to create a new field (HS2) but I also want the descriptions for these 2 digit codes from the codes table.
I know this should be easy but I can't get a subquery to work. I'm in Access 2003.
I can post the SQL if it helps but I think I'd just confuse the simple question.
CODES has two fields, codes and descriptions. The code field has the full hierarchy of codes, ie 1 digit, 2 digit, 4 digit, 8 digits. DATA is a very big dataset that I'm trying to link to and so I want to do this in one query, not two
The main data table has only 8 digit codes in it (so linking the two tables and getting the description for them is no problem). I use the left function in the query to get the two first digits to create a new field (HS2) but I also want the descriptions for these 2 digit codes from the codes table.
I know this should be easy but I can't get a subquery to work. I'm in Access 2003.
I can post the SQL if it helps but I think I'd just confuse the simple question.