I have a small table I am trying to join with a large table, and the smaller table [table1] has has a field [field1] containing doc numbers such as "BPI-1111"; however the larger table [table2] has doc numbers in [field2] that start like [field1] but have training characters that always start with a space "BPI-1111 (787N4-1111)". I need to join these two tables by these fields.
I know that there would normally be a % after the text in the second part of the like statement, but I'm not sure how to or if it is even possible to use the % variable with a field. I know that I'm misusing the % sign below, but I don't know where else to put it. Can someone help me with the like statement or tell me what I should do differently? Thanks,
I need this ASAP, so thank you in advance for your help...
FROM [table2] INNER JOIN [table2] ON [table1].[field1] = [table2].[field2]
WHERE ((([table2].[field2]) Like '[table1].[field1]%' ));