By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,696 Members | 2,213 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,696 IT Pros & Developers. It's quick & easy.

append query doesn't append due to key violations

P: n/a
It doesn't append anything to the primary key (autonumber, long
integer), only to all the other fields. Even if I set all the fields to
allow zero length: yes, indexed: no and required no. All the field
names are the same in the query as in the destination table.

This is the query:

INSERT INTO tblTempPackagesImp ( PackQuantImp, PackTypeImpID,
GrossWeightImp, GrossCubeImp, CommodityName, ContainerID, BolImportID,
ContainerSealNumber, UnnoImp, ClassImp, TempImp, ContainerPosition )

SELECT BICNTRP.CTR_PKGQ AS PackQuantImp,
qrySelectPackTypeImpID.PackTypeImpID, BICNTRP.CTR_GWT AS
GrossWeightImp, BICNTRP.CTR_MSMT AS GrossCubeImp, "Type commodity here"
AS CommodityName, qrySelectContainerID.ContainerID AS ContainerID,
qrySelectBolImportID.BolImportID, BICNTRP.CTR_SEAL AS
ContainerSealNumber, BICNTRP.CTR_UNNO AS UnnoImp, Null AS ClassImp,
IIf([BICNTRP.CTR_SZTP]="4RH","-18.0C",Null) AS TempImp, Null AS
ContainerPosition

FROM ((((tblBolImport RIGHT JOIN qrySelectBolImportNo ON
tblBolImport.BolImportNo = qrySelectBolImportNo.BolImportNo) LEFT JOIN
BICNTRP ON qrySelectBolImportNo.BKD_BLNO = BICNTRP.BKD_BLNO) LEFT JOIN
qrySelectPackTypeImpID ON (BICNTRP.BKD_BLNO =
qrySelectPackTypeImpID.BKD_BLNO) AND (BICNTRP.CTR_CNTR =
qrySelectPackTypeImpID.CTR_CNTR)) LEFT JOIN qrySelectBolImportID ON
(BICNTRP.BKD_BLNO = qrySelectBolImportID.BKD_BLNO) AND
(BICNTRP.CTR_CNTR = qrySelectBolImportID.CTR_CNTR)) LEFT JOIN
qrySelectContainerID ON (BICNTRP.BKD_BLNO =
qrySelectContainerID.BKD_BLNO) AND (BICNTRP.CTR_CNTR =
qrySelectContainerID.CTR_CNTR)

WHERE (((tblBolImport.BolImportNo) Is Null) AND
((qrySelectBolImportID.BolImportID)<>0));

It shows 463 records. All don't append. Can anyone tell me why? I'm
clueless. Is it the joins? Any pointers would be appreciated.

Dec 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you are not appending to your primary key and your primary key is
not an autonumber field, then that could be why you are you are getting
the error. The primary key field cannot be null. Make sure that your
PK really is an autonumber or that the PK does not include fields that
you are neglecting to append to.
a_*********@hotmail.com wrote:
It doesn't append anything to the primary key (autonumber, long
integer), only to all the other fields. Even if I set all the fields to
allow zero length: yes, indexed: no and required no. All the field
names are the same in the query as in the destination table.

This is the query:

INSERT INTO tblTempPackagesImp ( PackQuantImp, PackTypeImpID,
GrossWeightImp, GrossCubeImp, CommodityName, ContainerID, BolImportID,
ContainerSealNumber, UnnoImp, ClassImp, TempImp, ContainerPosition )

SELECT BICNTRP.CTR_PKGQ AS PackQuantImp,
qrySelectPackTypeImpID.PackTypeImpID, BICNTRP.CTR_GWT AS
GrossWeightImp, BICNTRP.CTR_MSMT AS GrossCubeImp, "Type commodity here"
AS CommodityName, qrySelectContainerID.ContainerID AS ContainerID,
qrySelectBolImportID.BolImportID, BICNTRP.CTR_SEAL AS
ContainerSealNumber, BICNTRP.CTR_UNNO AS UnnoImp, Null AS ClassImp,
IIf([BICNTRP.CTR_SZTP]="4RH","-18.0C",Null) AS TempImp, Null AS
ContainerPosition

FROM ((((tblBolImport RIGHT JOIN qrySelectBolImportNo ON
tblBolImport.BolImportNo = qrySelectBolImportNo.BolImportNo) LEFT JOIN
BICNTRP ON qrySelectBolImportNo.BKD_BLNO = BICNTRP.BKD_BLNO) LEFT JOIN
qrySelectPackTypeImpID ON (BICNTRP.BKD_BLNO =
qrySelectPackTypeImpID.BKD_BLNO) AND (BICNTRP.CTR_CNTR =
qrySelectPackTypeImpID.CTR_CNTR)) LEFT JOIN qrySelectBolImportID ON
(BICNTRP.BKD_BLNO = qrySelectBolImportID.BKD_BLNO) AND
(BICNTRP.CTR_CNTR = qrySelectBolImportID.CTR_CNTR)) LEFT JOIN
qrySelectContainerID ON (BICNTRP.BKD_BLNO =
qrySelectContainerID.BKD_BLNO) AND (BICNTRP.CTR_CNTR =
qrySelectContainerID.CTR_CNTR)

WHERE (((tblBolImport.BolImportNo) Is Null) AND
((qrySelectBolImportID.BolImportID)<>0));

It shows 463 records. All don't append. Can anyone tell me why? I'm
clueless. Is it the joins? Any pointers would be appreciated.
Dec 6 '06 #2

P: n/a
The primary key is an autonumber field, so it can't be null. Anything
else to doublecheck?
Jeff L schreef:
If you are not appending to your primary key and your primary key is
not an autonumber field, then that could be why you are you are getting
the error. The primary key field cannot be null. Make sure that your
PK really is an autonumber or that the PK does not include fields that
you are neglecting to append to.
a_*********@hotmail.com wrote:
It doesn't append anything to the primary key (autonumber, long
integer), only to all the other fields. Even if I set all the fields to
allow zero length: yes, indexed: no and required no. All the field
names are the same in the query as in the destination table.

This is the query:

INSERT INTO tblTempPackagesImp ( PackQuantImp, PackTypeImpID,
GrossWeightImp, GrossCubeImp, CommodityName, ContainerID, BolImportID,
ContainerSealNumber, UnnoImp, ClassImp, TempImp, ContainerPosition )

SELECT BICNTRP.CTR_PKGQ AS PackQuantImp,
qrySelectPackTypeImpID.PackTypeImpID, BICNTRP.CTR_GWT AS
GrossWeightImp, BICNTRP.CTR_MSMT AS GrossCubeImp, "Type commodity here"
AS CommodityName, qrySelectContainerID.ContainerID AS ContainerID,
qrySelectBolImportID.BolImportID, BICNTRP.CTR_SEAL AS
ContainerSealNumber, BICNTRP.CTR_UNNO AS UnnoImp, Null AS ClassImp,
IIf([BICNTRP.CTR_SZTP]="4RH","-18.0C",Null) AS TempImp, Null AS
ContainerPosition

FROM ((((tblBolImport RIGHT JOIN qrySelectBolImportNo ON
tblBolImport.BolImportNo = qrySelectBolImportNo.BolImportNo) LEFT JOIN
BICNTRP ON qrySelectBolImportNo.BKD_BLNO = BICNTRP.BKD_BLNO) LEFT JOIN
qrySelectPackTypeImpID ON (BICNTRP.BKD_BLNO =
qrySelectPackTypeImpID.BKD_BLNO) AND (BICNTRP.CTR_CNTR =
qrySelectPackTypeImpID.CTR_CNTR)) LEFT JOIN qrySelectBolImportID ON
(BICNTRP.BKD_BLNO = qrySelectBolImportID.BKD_BLNO) AND
(BICNTRP.CTR_CNTR = qrySelectBolImportID.CTR_CNTR)) LEFT JOIN
qrySelectContainerID ON (BICNTRP.BKD_BLNO =
qrySelectContainerID.BKD_BLNO) AND (BICNTRP.CTR_CNTR =
qrySelectContainerID.CTR_CNTR)

WHERE (((tblBolImport.BolImportNo) Is Null) AND
((qrySelectBolImportID.BolImportID)<>0));

It shows 463 records. All don't append. Can anyone tell me why? I'm
clueless. Is it the joins? Any pointers would be appreciated.
Dec 6 '06 #3

P: n/a
Also, be sure that none of the fields you are leaving null or
zero-length are not foreign keys to other tables -- meaning that field
must tie back to the primary key in another table. That will also
trigger the key violation message.

Dec 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.