Import Duplicates

[dbay]

I have two tables. Table1 is a list that I import from an Excel sheet, Table2 is a table that I append the list from Table1 into. I need to check to see if the items from Table1 exists in Table2 before appending them so there are no duplicates. The duplicates are determined by two fields, IdexID and VIN. The VIN can be repeated as long as its IndexId is not the same. The IndexId is determined by its parent record it belongs to.

Example:
NOT OK
IndexId 1, VIN XYZ
IndexId 1, VIN XYZ
IndexId 1, VIN XYZ

OK
IndexId 1, VIN XYZ
IndexId 2, VIN XYZ
IndexId 3, VIN XYZ

 

[Tidy]

I would put the results of the data you pull into a query and select "Unique Records = Yes" this should remove the duplicate entries.

 

[dbay]

I would put the results of the data you pull into a query and select "Unique Records = Yes" this should remove the duplicate entries.

I need to compare Table1 to Table2 before any data is inserted into Table2, and then just insert the records that are not duplicates. If I run a query after I combine the two tables and remove deplicates, I will lose existing data in Table2. As far as Unique Records, there is none. The Uniqueness comes from a combination of the IndexId and the VIN fields.

 

[WayneRyan]

dbay,

Insert Into Table2 (IndexId, VIN)
Select A.IndexID, A.VIN
From   Table1 As A Left Join Table2 As B On
           A.IndexID = B.IndexId And
           A.VIN = B.VIN
Where  B.IndexID Is Null

Wayne

 

[dbay]

dbay,

Insert Into Table2 (IndexId, VIN)
Select A.IndexID, A.VIN
From   Table1 As A Left Join Table2 As B On
           A.IndexID = B.IndexId And
           A.VIN = B.VIN
Where  B.IndexID Is Null

Wayne

Looks good...Will have to try it out when I get home tonight...

 

[dbay]

dbay,

Insert Into Table2 (IndexId, VIN)
Select A.IndexID, A.VIN
From   Table1 As A Left Join Table2 As B On
           A.IndexID = B.IndexId And
           A.VIN = B.VIN
Where  B.IndexID Is Null

Wayne

Ok Wayne,

I tried the statement above and it works perfectly for what it is intended for. But, now I need to modify it. I need the IndexID to come from the FORM because it does not exist in the Table1(tblTempList).

Example:

Dim IndexID As Integer
IndexID = Me.ActionIndex.Value


DoCmd.RunSQL "INSERT INTO tblActionVINList ([ActionIndex], [VINumber]) SELECT A.IndexID, A.F7 FROM tblTempList AS A LEFT JOIN tblActionVINList AS B ON A.IndexID = B.ActionIndex AND A.F7 = B.VINumber WHERE B.ActionIndex IS Null;"

 

[WayneRyan]

dbay,

Insert Into Table2 (IndexId, VIN)
Select A.IndexID, A.VIN
From   Table1 As A Left Join Table2 As B On
           A.IndexID = B.IndexId And
           A.VIN = B.VIN
Where  A.IndexID = Forms!YourForm!ActionIndex And
          B.IndexID Is Null

Wayne

 

[dbay]

dbay,

Insert Into Table2 (IndexId, VIN)
Select A.IndexID, A.VIN
From   Table1 As A Left Join Table2 As B On
           A.IndexID = B.IndexId And
           A.VIN = B.VIN
Where  A.IndexID = Forms!YourForm!ActionIndex And
          B.IndexID Is Null

Wayne

Will try it, thank you very much Wayne!