title: Tricky MS Access SQL query to remove surplus duplicate records
body: <p>I have an Access table of the form (I'm simplifying it a bit)</p>
  
  <pre><code>ID            AutoNumber       Primary Key
  SchemeName    Text (50)
  SchemeNumber  Text (15)
  </code></pre>
  
  <p>This contains some data eg...</p>
  
  <pre><code>ID            SchemeName           SchemeNumber
  --------------------------------------------------------------------
  714           Malcolm              ABC123
  80            Malcolm              ABC123
  96            Malcolms Scheme      ABC123
  101           Malcolms Scheme      ABC123
  98            Malcolms Scheme      DEF888
  654           Another Scheme       BAR876
  543           Whatever Scheme      KJL111
  etc...
  </code></pre>
  
  <p>Now. I want to remove duplicate names under the same SchemeNumber. But I want to leave the record which has the longest SchemeName for that scheme number. <em>If there are duplicate records with the same longest length then I just want to leave only one, say, the lowest ID (but any one will do really)</em>. From the above example I would want to delete IDs 714, 80 and 101 (to leave only 96).</p>
  
  <p>I thought this would be relatively easy to achieve but it's turning into a bit of a nightmare! Thanks for any suggestions. I know I could loop it programatically but I'd rather have a single DELETE query.</p>