Matching values between 2 tables using IN against a Long Text field

Question

I am currently using Access 2016 and am trying to create a query that is pulling in a different query and a table as its 2 source elements. Below is an example of the structure

table:
entity           code

legal            AP01
admin            AP02
acct             AP03


query1:
date             total            billing

1/1/2019         $10              000000-AP01-abcxyz
1/5/2019         $12              000000-AP01-abcxyz
1/12/2019        $15              000000-AP02-abcxyz

I've tried thinking about how to do a join, but since the billing field is a long text due to the fact that some strings are much larger than 255 characters, that is out of the question. So maybe using IN somehow, and the query would look for the code field value in table within the billing field value in query1, and display the following output

    query2:
    date             total            billing                    entity

    1/1/2019         $10              000000-AP01-abcxyz         legal
    1/5/2019         $12              000000-AP01-abcxyz         legal
    1/12/2019        $15              000000-AP02-abcxyz         admin

Using that output I could group by entity and sum total to show total spend within a department. I dont want to have to extract down to excel, run vlookup and find, then re-import it back in to access. There would be no point if I wanted to just do it all in excel. Can this be done within an access query?

Answer 1

You can use instr():

select q1.*, t1.entity
from q1 join
     t1
     on instr(q1.billing, t1.code) > 0