Duplicate entries on import - how do I stop it?

[shabbaranks]

Hi guys,

I have a query which looks up a table and appends any additions which arent in the table. The problem is its importing duplicates can anyone tell me how I do an "if doesnt exist" on a column please? My sql is below:

INSERT INTO ProjectsTable ( NAME, REFERENCE, NAME2 )
SELECT ProjectsLinked.NAME, ProjectsLinked.REFERENCE, ProjectsLinked.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable ON ProjectsLinked.NAME = ProjectsTable.NAME
WHERE (((ProjectsLinked.NAME2)="Active research centre") AND ((ProjectsTable.REFERENCE) Is Null And (ProjectsTable.REFERENCE)="!CENG98" And (ProjectsTable.REFERENCE)="!CENG01" And (ProjectsTable.REFERENCE)="!CENG99")) OR (((ProjectsLinked.NAME2)="!Sub-Project")) OR (((ProjectsLinked.NAME2)="Master Project"));

Thanks

 

[nanscombe]

I tend to use an Update query to do this sort of thing. It's not exactly identical to yours but you should be able to see where it's leading.

This one will add records from ProjectsLinked to ProjectsTable where no record exists with a matching NAME.

UPDATE ProjectsLinked LEFT JOIN ProjectsTable ON ProjectsLinked.NAME = ProjectsTable.NAME SET ProjectsTable.NAME = [ProjectsLinked].[NAME], ProjectsTable.REFERENCE = [ProjectsLinked].[REFERENCE], ProjectsTable.NAME2 = [ProjectsLinked].[NAME2]
WHERE (((ProjectsTable.NAME) Is Null));

 

[shabbaranks]

This works - kinda. It stops importing duplicates, but it wont import the data with "Master Project" in either I amended it to see if it would make a difference also - but it hasnt

UPDATE ProjectsLinked LEFT JOIN ProjectsTable ON ProjectsLinked.NAME = ProjectsTable.NAME SET ProjectsTable.NAME = [ProjectsLinked].[NAME], ProjectsTable.REFERENCE = [ProjectsLinked].[REFERENCE], ProjectsTable.NAME2 = [ProjectsLinked].[NAME2]
WHERE (((ProjectsTable.NAME) Is Null) AND ((ProjectsLinked.NAME2)="Active Research Centre") AND ((ProjectsLinked.REFERENCE)="!CENG98")) OR (((ProjectsLinked.NAME2)="Master Project") AND ((ProjectsLinked.REFERENCE)="!CENG99")) OR (((ProjectsLinked.REFERENCE)="!CENG01"));