title: MS Access 2016 left join creating duplicate records
body: <p>I'm new to MS Access and need to add the Group Name field from table 2 to table 1 (see below).</p>
  <p>Table 1 lists all invoice line item transactions.  Table 2 lists pricing categories by location and boat length.  The group name field in table 2 must match the location code in table 1 and the boat length from table 1 must fall between the min and max lengths from table 2.</p>
  <p>I created a query with a left join and used the expression builder to make sure the group name pulled into table 1 meets the length requirements in table 2.</p>
  <pre><code>SELECT 
      q_Step2_AMR.*, 
      IIf(
          [HS Boat Length]&lt;=[t_BLG]![Max] And [HS Boat Length]&gt;=[t_BLG]![Min],
          [t_BLG]![Group Name],
          &quot;test&quot;
      ) AS [Local Boat Length Group] 
  FROM q_Step2_AMR 
  LEFT JOIN t_BLG ON q_Step2_AMR.[Location Code] = t_BLG.[Location Code]
  </code></pre>
  <p>The problem is it creates duplicate records for each record from table 1 for every time there is a location code in table 2 that matches the location code in a record from table 1.</p>
  <p><img src="https://i.stack.imgur.com/HrA4p.png" alt="enter image description here" /></p>