title: how to find unmatched records with multiple match options
body: <p>In MS Access. I have two tables. I am trying to find all records from Table 1 that do not have a match in Table 2.</p>
  
  <pre><code>Table 1
  
  DP     FY     BS     RM     EX    
  --------------------------------
  21     1     0107     0    3000   
  20     R     0201     6    3960   
  12     3     0165     A    1111   
  
  
  Table 2
  
  DP     FY     BS     RD     LI
  -------------------------------
  11     4     0300    0     **11
  21     5     0107    7     8**W
  97     7     0819    0     2***
  21     4     0107    7     2011
  </code></pre>
  
  <p>I need to concatenate Table1.DP + Table1.FY + Table1.BS + Table1.RM + Table1.EX and find any records in Table1 where there is NO MATCH in table2 on columns Table2.DP + Table2.FY + Table2.BS + Table2.RD + Table2.LI.</p>
  
  <p>I also need no match on a few substrings of table2.LI where they will contain asterisks (not wildcards, actual asterisks). </p>
  
  <p>The left 2 characters of Table2.LI could be asterisks, in which case I want to return that row in my result,</p>
  
  <p>The middle 2 characters of Table2.LI could be asterisks, in which case I want to return that row in the result.</p>
  
  <p>The last 3 characters of Table2.LI could be asterisks, in which case I want to return that row in my result.</p>
  
  <p>So, if we were to concatenate the 5 key fields into one 12 character text field named Key, I want a query that will return all results from Table1 where </p>
  
  <pre><code>Table1.Key &lt;&gt; Table2.Key
  Left(Table1.Key,8) + "**" + Right(Table1.Key,2) &lt;&gt; Table2.Key
  Left(Table1.Key,9) + "**" + Right(Table1.Key,1) &lt;&gt; Table2.Key
  Left(Table1.Key,9) + "***" &lt;&gt; Table2.Key
  </code></pre>
  
  <p>Basically, in those instances where there are asterisks in those substrings of Table2.LI, I do not care what is in Table1.EX in those positions.</p>
  
  <p>How can I write a SQL statement to give me those results of an UNMATCHED query where I only get results of Table 1 where there is NO MATCH on those conditions in Table 2? Can I write the query to see the asterisks in the field as wildcards for comparison?</p>
  
  <p><strong>Update</strong>: I was able to solve the issue by replacing asterisks with question marks in the key field after building the key field. Then doing a left join using "like" on the join clause rather than equals. Now I have a newly discovered issue. There are two conditions where there are asterisks in the LI field but I do not want them converted to question marks because I don't want them seen as wildcards. </p>
  
  <p>These are the conditions I DO want to change. Table2.LI =</p>
  
  <pre><code>**XX
  X**X
  X***
  </code></pre>
  
  <p>These are the conditions I do NOT want to be turned into wildcards: Table2.LI =</p>
  
  <pre><code>****
  X*XX
  </code></pre>
  
  <p>(where X is any number or letter).</p>
  
  <p>I was able to resolve the first by just adding a where clause saying where Table2.LI NOT CONTAINS("****"). Now I'm just trying to resolve the last condition. I need to check the LI field for an asterisk in the second position only, and ignore that case.</p>