Microsoft Access - Linking fields in two tables that are only partially the same?

Asked By Nate on 13-Mar-13 05:44 PM
Hello, I'm trying to join two tables into one, merging values in a way I'm not sure how to implement.

I have two tables a bit like this:



Table 1:

Field1      Field2    Field3   Field4 ...

0001abc      data....

0001def       data....

0002abc      data....

0002def         data.... 

0003abc      data....

0003def       data....



Table 2:

Field1      Field2    Field3   Field4 ...

0001xyz      Apples

0002xyz      Oranges

0003xyz    Bananas



What I would like to do is create a query that displays all the fields in Table 1 but also displays Field 2 from table two, based on the partial match of the first 4 characters (so both 0001abc and 0001def would have a field with "Apples" in it etc.)



Is something like this possible?