title: MS Access - Remove duplicates based on three columns while keeping one result without using SQL Min/Max
body: <p>I have been having an issue for a little bit of time now and cannot find a solution that has worked for me.  It might be that I am just not doing it correctly or that there is an alternative that will work better.  I am opened to, and appreciative, of any ideas.</p>
  
  <p>I have a table (tblDocQueue) in access that is like the one displayed below.  The data comes from a data extract of an older application that we use at work, so the source or the extract cant be changed.  We upload the data to Access to analyze and build metrics around it.  The tables is as follows:</p>
  
  <p>ID    DocName    OwnerName    AccountNum    DocRef <br/>
  1      Doc 1       Matt         1001        Z0005638<br/>
  2      Doc 1       Matt         1002        Z0005638<br/>
  3      Doc 1       Tony         5010        Z0005639<br/>
  4      Doc 2       Luke         1050        Z0005640<br/>
  5      Doc 3       Luke         1050        Z0005641<br/>
  6      Doc 3       Gary         1234        Z0005641<br/>
  7      Doc 4       John         8789        Z0005642<br/>
  8      Doc 5       Ed           8789        Z0005642<br/>
  9      Doc 5       Ed           8790        Z0005643<br/>
  10     Doc 5       Connie       4579        Z0005644<br/>
  11     Doc 6       Mary         3616        Z0005645<br/>
  12     Doc 6       Lucy         4795        Z0005646<br/>
  13     Doc 6       Tina         4795        Z0005646<br/>
  14     Doc 7       Matt         1001        Z0005638<br/>
  15     Doc 7       John         8789        Z0005647<br/></p>
  
  <p>There are more columns than what are listed, but they are not relevant to the question.  I am trying to remove duplicates, keeping one unique value, based on three columns (DocName, OwnerName, Doc Ref).  I was doing this using the following SQL, but it began taking hours (up to 7) to process around 500,000 lines of data.  I am unsure if the efficiency problem is because of using min/max or some other reason</p>
  
  <pre><code>SELECT tblDocQueue.ID AS Expr1, tblDocQueue.DocName AS Expr2,
      tblDocQueue.OwnerName AS Expr3, tblDocQueue.AcctNo AS Expr4,
      tblDocQueue.ExpDate AS Expr5, tblDocQueue.EffectiveDate AS Expr6,
      tblDocQueue.SignatureDate AS Expr7, tblDocQueue.DocBNYSts AS Expr8,
      tblDocQueue.StsDate AS Expr9, tblDocQueue.UserSts AS Expr10,
      tblDocQueue.Location AS Expr11, tblDocQueue.Ackngmt AS Expr12,
      tblDocQueue.OPID AS Expr13, tblDocQueue.Comments AS Expr14,
      tblDocQueue.DocRef AS Expr15, tblDocQueue.ExternalComment AS Expr16,
      tblDocQueue.FirstName AS Expr17, tblDocQueue.LastName AS Expr18,
      tblDocQueue.ClientID AS Expr19, tblDocQueue.Address AS Expr20,
      tblDocQueue.CountryCode AS Expr21
  FROM tblDocQueue
  WHERE ((([tblDocQueue].[ID])=(
      SELECT Min(t.[ID]) 
      FROM [tblDocQueue] AS t 
      WHERE t.[DocRef]=[tblDocQueue].[DocRef] 
       AND t.[DocName]=[tblDocQueue].[DocName])));
  </code></pre>
  
  <p>The time that this it was taking was unacceptable for the business team.  I then developed a work around in VBA that exports the data to an excel file, using the remove duplicates function preloaded in excel, and imported the unique data back into a different table.  This only takes a few seconds in excel.  As the use of this database is beginning to expand, and I will removing duplicates in a similar way for hundreds of datasets a day, I am trying to get this to work without using the above workaround.</p>
  
  <p>The anticipated result of the above example data would be:</p>
  
  <p>ID     DocName     OwnerName     AccountNum      DocRef<br/>
  1   Doc 1   Matt    1001    Z0005638    <br/>
  3   Doc 1   Tony    5010    Z0005639    <br/>
  4   Doc 2   Luke    1050    Z0005640    <br/>
  5   Doc 3   Luke    1050    Z0005641    <br/>
  6   Doc 3   Gary    1234    Z0005641    <br/>
  7   Doc 4   John    8789    Z0005642    <br/>
  8   Doc 5   Ed  8789    Z0005642    <br/>
  9   Doc 5   Ed  8790    Z0005643    <br/>
  10  Doc 5   Connie  4579    Z0005644    <br/>
  11  Doc 6   Mary    3616    Z0005645    <br/>
  12  Doc 6   Lucy    4795    Z0005646    <br/>
  13  Doc 6   Tina    4795    Z0005646    <br/>
  14  Doc 7   Matt    1001    Z0005638    <br/>
  15  Doc 7   John    8789    Z0005647    <br/></p>
  
  <p>If anyone can help me with the SQL to:</p>
  
  <ol>
  <li>Conditionally remove duplicate values based on three columns</li>
  <li>Using Microsoft Access 2010</li>
  <li>While keeping one row of each unique value</li>
  <li>In some way that is efficient / does not take a long amount of time (tables up to 5,000,000 records)</li>
  </ol>
  
  <p>Any and all help is greatly appreciated!</p>