Append query, problem with key violation

[FoolzRailer]

Hello



I'm trying to run an append query, but I keep getting key violations. I'm trying to update the field SaneringmetKode in ProjektDelledning using DelledningsID as a join criteria.

INSERT INTO ProjektDelledning ( ProjektID, DelledningID, SaneringsmetKode, DatoOprettet, DatoOpdateret )

SELECT  ProjektDelledning.ProjektID, ProjektDelledning.DelledningID,  NySaneringsData.SaneringsmetodeKode AS SaneringsmetKode,  Nz([ProjektDelledning].[DatoOprettet],Now()) AS DatoOprettet,  Nz([ProjektDelledning].[DatoOpdateret],Now()) AS DatoOpdateret

FROM NySaneringsData INNER JOIN ProjektDelledning ON NySaneringsData.DelledningsID = ProjektDelledning.DelledningID

WHERE  (((Exists (SELECT * FROM ProjektDelledning WHERE  ProjektDelledning.SaneringsmetKode = NySaneringsData.SaneringsmetodeKode  AND ProjektDelledning.DelledningID =  NySaneringsData.DelledningsID))=False));

If I try to remove the ProjektID I get validation rule violation instead. Any help would be much appreciated!

[CJ_London]

you are trying to insert a new primary key that already exists

If I try to remove the ProjektID I get validation rule violation instead

because this is part of your primary key, it has to be completed, it cannot be null.

Also to clarify, your sql code has an inner join, but the query view shows a left or right join - which is it?

Also, if I have read your subquery correctly, you don't need it

If it is supposed to be a left join, try

INSERT INTO ProjektDelledning ( ProjektID, DelledningID, SaneringsmetKode, DatoOprettet, DatoOpdateret )

SELECT  ProjektDelledning.ProjektID, NySaneringsData.DelledningID,  NySaneringsData.SaneringsmetodeKode AS SaneringsmetKode,  Nz([ProjektDelledning].[DatoOprettet],Now()) AS DatoOprettet,  Nz([ProjektDelledning].[DatoOpdateret],Now()) AS DatoOpdateret

FROM NySaneringsData LEFT JOIN ProjektDelledning ON NySaneringsData.DelledningsID = ProjektDelledning.DelledningID

WHERE  ProjektDelledning.DelledningID IS NULL

[John_G]

I'm trying to update the field SaneringmetKode in ProjektDelledning using DelledningsID as a join criteria.

update the field: What do you mean by that? Are you trying to update fields in records that already exist, or are you trying to add new records to the table?

If you want to add data to blank fields in records that already exist, you need to use an UPDATE query, not an APPEND.

Can you clarify this, please?