title: MS Access 2007 update/insert or merge or union
body: <p>I have been searching for quite a while for how to do something that should be pretty easy to do in MS Access 2007.</p>
  
  <p>I have a main table, items_moved, that tracks the number of files received, by type, by day.  The basic structure is: </p>
  
  <p>ID (autonumber), drive (text), type (text), date (DateTime), file_count (Number), file_size (Number)</p>
  
  <p>Typical data looks like:</p>
  
  <pre><code>1777, F:\snaps, pics, 6/09/2010, 66, 151616131
  1778, F:\snaps, pics, 6/10/2010, 5, 464864
  1779, G:\pics, pics, 6/09/2010, 58, 45645646
  1780, G:\pics, pics, 6/10/2010, 70, 123456667
  </code></pre>
  
  <p>I have a temp table with the exact same data structure.  The temp table is generated by starting with (and including) the last day of the items_moved table and finding what's new.</p>
  
  <p>Typical data looks like:</p>
  
  <pre><code>1, F:\snaps, pics, 6/10/2010, 366, 6531616131
  2, F:\snaps, pics, 6/11/2010, 5, 464864
  3, G:\pics, pics, 6/10/2010, 70, 123456667
  4, G:\pics, pics, 6/11/2010, 56, 123645964
  </code></pre>
  
  <p>All I'm trying to do is append the temp table on to the main table so that:</p>
  
  <ol>
  <li>There are no duplicates (based on drive and date)</li>
  <li>If the temp table has matching drive and date and larger values it overwrites the main table</li>
  <li>If the row exists in the temp table but not the main table, it gets appended to the end of the main .</li>
  </ol>
  
  <p>The result would be in the items_moved table which would records 1-1776 unchanged, but now end like this:</p>
  
  <pre><code>1777, F:\snaps, pics, 6/09/2010, 66, 151616131         (Unchanged)
  1778, F:\snaps, pics, 6/10/2010, 366, 6531616131       (Updated temp was larger)
  1779, G:\pics, pics, 6/09/2010, 58, 45645646           (unchanged)
  1780, G:\pics, pics, 6/10/2010, 70, 123456667          (unchanged)
  1781, F:\snaps, pics, 6/11/2010, 5, 464864             (added)
  1782, G:\pics, pics, 6/11/2010, 56, 123645964          (added)
  </code></pre>
  
  <p>I've tried every variation on join that I can think of.</p>
  
  <p>I can get the rows with matching dates, so I can use that for an update statement.  I can't get the rows from the temp that don't have matching dates in the items_moved table, so I can't run the insert.</p>
  
  <p>The final point is to execute this as part of a VB action when the user requests a report from the access db.</p>
  
  <p>Here's a snippet</p>
  
  <pre><code>INSERT INTO items_moved (drive, type, file_date, file_count, file_size) 
   SELECT 'F:\snaps','pics', temp_table.created, Count(temp_table.created),
    Sum(temp_table.size) FROM temp_table where temp_table.drive = 'F:\snaps' 
    GROUP BY temp_table.created
  </code></pre>
  
  <p>This works, but just adds to the end of the table.  I'm left with the problem of duplicates.  I'm sure there's a way to de-duplicate the table on the fly, but that seems like a waste.  I've found some hints on using <strong>union</strong> , but nothing on how to use union to update one of the tables in the union.</p>
  
  <p>Any input would be appreciated.  Thanks.</p>