title: How to only select rows that have not been soft deleted in a SQL Query?
body: <p>I am trying to deduplicate my companies database of companies.</p>
  <p>The database administrator left for a new position and has not been replaced.
  So I as the IT support guy have been roped into babysitting it for the time being.</p>
  <p>I am having trouble with this query in MS Access.</p>
  <pre><code>SELECT dbo_Companies.PostCode, 
  dbo_Companies.Phone, 
  dbo_Companies.CpyID, 
  dbo_Companies.Name, 
  dbo_Companies.Building, 
  dbo_Companies.Street, 
  dbo_Companies.City, 
  dbo_Companies.Deleted
  FROM dbo_Companies
  WHERE (((dbo_Companies.PostCode) In (SELECT [PostCode] FROM [dbo_Companies] 
  As Tmp GROUP BY [PostCode],[Phone] 
  HAVING Count(*)&gt;=2  And [Phone] = [dbo_Companies].[Phone])) 
  AND ((dbo_Companies.Deleted)=False))
  ORDER BY dbo_Companies.PostCode, dbo_Companies.Phone;
  </code></pre>
  <p>It works mostly, the problem I am having is that it searches for records in which there are more than 2 instances of a post code and phone number, which is what I want.</p>
  <p>However I want it to not search through any soft deleted records, it does search for soft deleted records but then filters them out of the table it produces.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PostCode</th>
  <th>Phone</th>
  <th>CpyID</th>
  <th>Name</th>
  <th>Building</th>
  <th>Street</th>
  <th>City</th>
  <th>Deleted</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>AB12 4AX</td>
  <td>1224404407</td>
  <td>132931</td>
  <td>joe bloggs</td>
  <td>example text</td>
  <td>street 1</td>
  <td>Aberdeen</td>
  <td>FALSE</td>
  </tr>
  <tr>
  <td>AB12 4AX</td>
  <td>1224404407</td>
  <td>99338</td>
  <td>joe bloggs co</td>
  <td>example text</td>
  <td>street 1</td>
  <td>Aberdeen</td>
  <td>TRUE</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So I end up with loads of records which fit the criteria but only because of soft deleted records.</p>
  <p>Is there any way to make it only match records by postcode and phone that are non soft deleted records, rather then just filtering them out of the resulting table.</p>
  <p>Thank you to anyone in advanced.</p>
  <hr />
  <p>Running the query you suggested johey, I'm still encountering the same results. It outputs some records which fit my criteria and some single ones that don't.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PostCode</th>
  <th>Phone</th>
  <th>CpyID</th>
  <th>Name</th>
  <th>Building</th>
  <th>Street</th>
  <th>City</th>
  <th>Deleted</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>BB4 4PW</td>
  <td>01706223999</td>
  <td>26855</td>
  <td>DRIVER TRETT (Driver Group Plc)</td>
  <td>Driver House 4 St Crispin Way</td>
  <td>Haslingden</td>
  <td>Rossendale</td>
  <td>FALSE</td>
  </tr>
  <tr>
  <td>BB4 4PW</td>
  <td>01706223999</td>
  <td>210016</td>
  <td>Driver Trett</td>
  <td>Driver House 4</td>
  <td>4 St Crispin Way</td>
  <td>Rossendale</td>
  <td>FALSE</td>
  </tr>
  <tr>
  <td>BB4 7PA</td>
  <td>01706219444</td>
  <td>131303</td>
  <td>Jacobson Group</td>
  <td></td>
  <td></td>
  <td>Rossendale</td>
  <td>FALSE</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Jacobson Group should not be showing because the only record which matches on postcode and phone number has been soft deleted as shown below.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PostCode</th>
  <th>Phone</th>
  <th>CpyID</th>
  <th>Name</th>
  <th>Building</th>
  <th>Street</th>
  <th>City</th>
  <th>Deleted</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>BB4 7PA</td>
  <td>01706219444</td>
  <td>85544</td>
  <td>JACOBSON D&amp; SONS LTD</td>
  <td></td>
  <td>Bacop Road</td>
  <td>Rossendale</td>
  <td>True</td>
  </tr>
  </tbody>
  </table>
  </div>