title: Access/SQL: How to get the next closest date in Access query?
body: <p>I have two tables that I'd like to join and match a date from table <code>DATE_GAPS</code> to the closest next date from table <code>MISSING_DATES</code>. The tables are below:</p>
  
  <p><strong>Table <code>MISSING_DATES</code>:</strong></p>
  
  <pre><code>UPC  | Date
  -----+----------
  1111 | 8/18/2018
  1111 | 8/19/2018
  1111 | 8/25/2018
  1222 | 8/18/2018
  1222 | 9/1/2018
  </code></pre>
  
  <p><strong>Table <code>DATE_GAPS</code>:</strong></p>
  
  <pre><code>UPC  | Date
  -----+----------
  1111 | 8/16/2018
  1111 | 8/17/2018
  1111 | 8/20/2018
  1111 | 8/27/2018
  1222 | 8/20/2018
  1222 | 9/4/2018
  </code></pre>
  
  <p><strong>New Table:</strong></p>
  
  <pre><code>UPC  | Date      | Closest Next Date
  -----+-----------+------------------
  1111 | 8/18/2018 | 8/20/2018
  1111 | 8/19/2018 | 8/20/2018
  1111 | 8/25/2018 | 8/27/2018
  1222 | 8/18/2018 | 8/20/2018
  1222 | 9/1/2018  | 9/4/2018
  </code></pre>
  
  <p>Here is the script I currently have but it's taking too long to load:</p>
  
  <pre><code>SELECT MISSING_DATES.UPC
    , MISSING_DATES.DATE
    , (
        SELECT MIN(DATE_GAPS.DATE)
        FROM DATE_GAPS
        INNER JOIN MISSING_DATES
          ON DATE_GAPS.UPC = MISSING_DATES.UPC
        WHERE DATE_GAPS.DATE &gt; MISSING_DATES.DATE
      ) AS [Closest Next Date]
  FROM MISSING_DATES
    , DATE_GAPS
  ORDER BY MISSING_DATES.DATE
    , MISSING_DATES.UPC;
  </code></pre>
  
  <p>I can't figure out how to either optimize the current script or rewrite to get what I want to output. </p>
  
  <p>I have also tried to use the following code which works but it gives me the closest date (before or after):</p>
  
  <pre><code>SELECT TOP 1 MISSING_DATES.UPC, MISSING_DATES.DATE, 
  DATE_GAPS.DATE, DATE_GAPS.UPC 
  FROM MISSING_DATES INNER JOIN DATE_GAPS ON MISSING_DATES.UPC=DATE_GAPS.UPC 
  WHERE (((DATE_GAPS.UPC)=[MISSING_DATES].[UPC])) ORDER BY
  Abs(DateDiff("d",MISSING_DATES.DATE,DATE_GAPS.DATE));
  </code></pre>
  
  <p>Thanks in advance!</p>