title: Avoiding Duplicates when appending records in Access
body: <p>I am aware this has been asked multiple times, but for one reason or another the solutions are not working for me.</p>
  
  <p><strong>Database Layout:</strong></p>
  
  <p>I have Table1 (Scanner_Location) Who is getting data pulled from another table/ subform on a form (Scanner IBOB) * Holds Columns: FP#, Count, Location, Model_ID, PK-SL_ID</p>
  
  <p>Table2 (Scanner Detail) Holds Two of the three data columns: (FP#, Location PK-SN)
  Table3 (Scanner_Model) Holds the last data column, displayed in a subform. (PK-Model_ID)</p>
  
  <p>The user will input FP#, and location in one section of the form, then navigate to the subform, and select multiple Models, and enter the count (Textbox). Once Selected, they click an 'update' button that executes my queries. (Of which I have an update, AND an Append Query)</p>
  
  <p>The problem is, just using an update query doesn't add the records. And using an Append query creates duplicates of the existing data.</p>
  
  <p><strong>Here's how the flow carries out:</strong></p>
  
  <p>User selects Model 1 and Model 2 with a count of 4 and an FP# of 100. Clicks update. 
  The queries update, and the information enters correctly.</p>
  
  <p>User Selects the same models again (Model_Select), with the same FP# and count, the Table1 has the same information entered again, with a different primary key.</p>
  
  <p><strong>The goal:</strong></p>
  
  <p>The append query creates duplicates of existing data. I only want my update and/or append queries to:</p>
  
  <p>Update the existing data - Looking for anything with the same FP#
  Add any records that do not exist already (Looking at Model_ID and FP#)</p>
  
  <pre><code>INSERT INTO Scanner_Location ( Model_ID, FootPrints_Num, Location_ID, Scanner_Loc_Cnt ) 
  SELECT Scanner_Model.Model_ID, [Forms]![Scanner_IBOB]![fpNum_txt] AS [FP#],
      [Forms]![Scanner_IBOB]![Location_Cbo_main] AS Location, 
      [Forms]![Scanner_IBOB]![Scanner_Loc_CntTxt] AS [Count] 
      FROM Scanner_Detail 
      RIGHT JOIN Scanner_Model ON Scanner_Detail.Model_ID = Scanner_Model.Model_ID
      WHERE (((Scanner_Model.SM_Acc_Select)=True) 
          AND ((NOT Exists (SELECT * FROM Scanner_location 
      WHERE (((Forms!Scanner_IBOB!fpNum_txt)=Forms!Scanner_IBOB!fpNum_tx‌​t) 
          And ((Scanner_Model.SM_Acc_Select)=True)); ))=False));
  </code></pre>