title: MS Access - Upsert Query Inserting All Data (Including Duplicate)
body: <p>Probably a simple one for you however I'm clearly missing something (Lack of sleep and coffee thus far) Basically I have a mass import process based on multiple files to import event data into our database. Thing is the same data could appear in multiple spreadsheets so I import them all into a staging table and was attempting to use an upsert to insert new/update duplicates.</p>
  
  <p>However... It basically just inserts all the data even if it is a "duplicate"</p>
  
  <p><strong>How the data works:</strong></p>
  
  <p>The table the excel sheets are being inserted into are for event attendees. In order to determine whether an 'Attendee' is unique or not we check 3 criteria,</p>
  
  <p><strong><em>ContactID</em></strong> = Do they exist in the master data (This is FK - if they don't exist in contacts table they cannot be added as attendee)</p>
  
  <p><strong><em>EventID</em></strong> = What event are they attending</p>
  
  <p><strong><em>TypeID</em></strong> = What attendee type are they (Sponsor, Delegate, Speaker, etc...)</p>
  
  <p>I have another upsert query for another import process however that only checks 1 field and works fine, however as this is checking 3 and for some reason is not working as intended.</p>
  
  <p><strong><em>The following data is just a sample and the fields 'EventID', 'ContactID' and 'TypeID' are all Foreign Keys</em></strong></p>
  
  <p><strong>Sample Data Before Upsert:</strong></p>
  
  <pre><code>+-----------+---------+-----------+--------+---------+
  | StagingID | EventID | ContactID | TypeID | IsDupe? |
  +-----------+---------+-----------+--------+---------+
  |         1 |       1 |         1 |      1 | No      |
  |         2 |       1 |         2 |      1 | No      |
  |         3 |       1 |         3 |      2 | No      |
  |         4 |       1 |         2 |      1 | Yes     |
  |         5 |       2 |         1 |      3 | No      |
  |         6 |       2 |         2 |      2 | No      |
  |         7 |       2 |         2 |      1 | No      |
  |         8 |       2 |         3 |      1 | No      |
  |         9 |       3 |         1 |      1 | No      |
  |        10 |       3 |         2 |      1 | No      |
  |        11 |       3 |         3 |      2 | No      |
  |        12 |       3 |         3 |      3 | No      |
  |        13 |       3 |         4 |      1 | No      |
  |        14 |       3 |         2 |      1 | Yes     |
  |        15 |       2 |         3 |      1 | Yes     |
  +-----------+---------+-----------+--------+---------+
  </code></pre>
  
  <p><strong>Expected Results:</strong> <em>(Actual results is just all the data in the table above)</em></p>
  
  <pre><code>+------------+---------+-----------+--------+---------+
  | AttendeeID | EventID | ContactID | TypeID | IsDupe? |
  +------------+---------+-----------+--------+---------+
  |          1 |       1 |         1 |      1 | No      |
  |          2 |       1 |         2 |      1 | No      |
  |          3 |       1 |         3 |      2 | No      |
  |          4 |       2 |         1 |      3 | No      |
  |          5 |       2 |         2 |      2 | No      |
  |          6 |       2 |         2 |      1 | No      |
  |          7 |       2 |         3 |      1 | No      |
  |          8 |       3 |         1 |      1 | No      |
  |          9 |       3 |         2 |      1 | No      |
  |         10 |       3 |         3 |      2 | No      |
  |         11 |       3 |         3 |      3 | No      |
  |         12 |       3 |         4 |      1 | No      |
  +------------+---------+-----------+--------+---------+
  </code></pre>
  
  <p>Basically anyone in the first table with 'yes' is a 'duplicate' as they are the same person going to the same event with the same attendance type.
  <em>Note: The 'Duplicate' in the table is not a field, just a key for the purpose of this question</em></p>
  
  <pre><code>UPDATE Out_Data LEFT JOIN In_Data ON (Out_Data.TypeID = In_Data.TypeID) AND (Out_Data.ContactID = In_Data.ContactID) AND (Out_Data.EventID = In_Data.EventID) SET In_Data.EventID = [Out_Data]![EventID], In_Data.ContactID = [Out_Data]![ContactID], In_Data.TypeID = [Out_Data]![TypeID], In_Data.Entry = [Out_Data]![Entry];
  </code></pre>
  
  <p><strong>Also:</strong> Here is an Upsert Query I made that does actually do as intended:</p>
  
  <pre><code>UPDATE tbl_Contacts RIGHT JOIN tbl_STG_Suppression ON tbl_Contacts.Email = tbl_STG_Suppression.Email SET tbl_Contacts.Suppress = -1, tbl_Contacts.Email = [tbl_STG_Suppression]![Email];
  </code></pre>