Append Key Violation

  • Thread starter Geezer via AccessMonster.com
  • Start date
G

Geezer via AccessMonster.com

I have several seemingly similar Append Queries but not all of them are
working. In several I receive the message that x records are not appended
because of Key Violations. I import data from Excel spreadsheets to
temporary tables and then run append queries to add new records to the
appropriate permanent tables. Here's the setup for one of the appends that
works:

Temp table is [ImportHR] with no PK
Not related to any other table in the DB
Field to append from ImportHR is [Status]
Text
Required=No
Allow Zero Length=Yes
Indexed=No
Unicode Compression=No
IME Mode= No Control
IME Sentence Mode= None

Permenant Table is [HRStatusTbl] with an AutoNumber PK
HRStatusTbl is related to PersonTbl as one-to-many PersonTbl.[HR StatusID]
(many) HRStatusTbl.[HRStatusID] (1)
Field to append to HRStatusTbl is [HR Status]; AutoNumber PK and [HR Status]
are the only fields in the table
[HR Status]
Text
Required=No
Allow Zero Length= Yes
Indexed= Yes (No Duplicates)
Unicode Compression= No
IME Mode= No Control
IME Sentence Mode= None

I created a Find Unmatched Query "ImportHRWithoutMatchingHRStatusQry" with
the wizard having this SQL:

SELECT ImportHR.Status, HRStatusTbl.[HR Status]
FROM ImportHR LEFT JOIN HRStatusTbl ON ImportHR.Status = HRStatusTbl.[HR
Status]
WHERE (((HRStatusTbl.[HR Status]) Is Null));

The append query has this SQL:

INSERT INTO HRStatusTbl ( [HR Status] )
SELECT ImportHRWithoutMatchingHRStatusQry.Status
FROM ImportHRWithoutMatchingHRStatusQry;

This all works fine and indeed appends new Status's when present in the
imported data to ImportHR.

The following is the setup for one of the appends that is not working:

Same temp table: is ImportHR with no PK
Not related to any other table in the DB
Field to append from ImportHR is [Dept]
Text
Required=No
Allow Zero Length=Yes
Indexed=No
Unicode Compression=No
IME Mode= No Control
IME Sentence Mode= None

Permenant Table is [DeptTbl] with an AutoNumber PK
DeptTbl is related to PersonTbl as one-to-many PersonTbl.[DeptID] (1) DeptTbl.
[DeptID] (many)
Field to append to DeptTbl is [Department]; AutoNumber PK [DeptID] and
[Department] are only fields in this table
[Department]
Text
Required=No
Allow Zero Length= Yes
Indexed= Yes (No Duplicates)
Unicode Compression= No
IME Mode= No Control
IME Sentence Mode= None

I created a Find Unmatched Query "ImportHRWithoutMatchingDeptQry" with the
wizard having this SQL:

SELECT ImportHR.Dept, DeptTbl.Department
FROM ImportHR LEFT JOIN DeptTbl ON ImportHR.Dept = DeptTbl.Department
WHERE (((DeptTbl.Department) Is Null));

The append query has this SQL:

INSERT INTO DeptTbl ( Department )
SELECT ImportHRWithoutMatchingDeptQry.Dept
FROM ImportHRWithoutMatchingDeptQry;

When I run the append query simply as a Select query it returns the
appropriate values that should be appended, but when the append query is run
I get the message that no records were appended due to Key Violations. I
can't see any difference between the working HRStatus append query and the
not working Dept append query. Any thoughts?

Thanks
 
M

mscertified

This message means you have duplicate keys or indexes. You need to check the
DATA to make sure it does not contain duplicate values. Look at all the
columns to see if any contain the properties INDEXED DUPLICATES NOT ALLOWED.

-Dorian

Geezer via AccessMonster.com said:
I have several seemingly similar Append Queries but not all of them are
working. In several I receive the message that x records are not appended
because of Key Violations. I import data from Excel spreadsheets to
temporary tables and then run append queries to add new records to the
appropriate permanent tables. Here's the setup for one of the appends that
works:

Temp table is [ImportHR] with no PK
Not related to any other table in the DB
Field to append from ImportHR is [Status]
Text
Required=No
Allow Zero Length=Yes
Indexed=No
Unicode Compression=No
IME Mode= No Control
IME Sentence Mode= None

Permenant Table is [HRStatusTbl] with an AutoNumber PK
HRStatusTbl is related to PersonTbl as one-to-many PersonTbl.[HR StatusID]
(many) HRStatusTbl.[HRStatusID] (1)
Field to append to HRStatusTbl is [HR Status]; AutoNumber PK and [HR Status]
are the only fields in the table
[HR Status]
Text
Required=No
Allow Zero Length= Yes
Indexed= Yes (No Duplicates)
Unicode Compression= No
IME Mode= No Control
IME Sentence Mode= None

I created a Find Unmatched Query "ImportHRWithoutMatchingHRStatusQry" with
the wizard having this SQL:

SELECT ImportHR.Status, HRStatusTbl.[HR Status]
FROM ImportHR LEFT JOIN HRStatusTbl ON ImportHR.Status = HRStatusTbl.[HR
Status]
WHERE (((HRStatusTbl.[HR Status]) Is Null));

The append query has this SQL:

INSERT INTO HRStatusTbl ( [HR Status] )
SELECT ImportHRWithoutMatchingHRStatusQry.Status
FROM ImportHRWithoutMatchingHRStatusQry;

This all works fine and indeed appends new Status's when present in the
imported data to ImportHR.

The following is the setup for one of the appends that is not working:

Same temp table: is ImportHR with no PK
Not related to any other table in the DB
Field to append from ImportHR is [Dept]
Text
Required=No
Allow Zero Length=Yes
Indexed=No
Unicode Compression=No
IME Mode= No Control
IME Sentence Mode= None

Permenant Table is [DeptTbl] with an AutoNumber PK
DeptTbl is related to PersonTbl as one-to-many PersonTbl.[DeptID] (1) DeptTbl.
[DeptID] (many)
Field to append to DeptTbl is [Department]; AutoNumber PK [DeptID] and
[Department] are only fields in this table
[Department]
Text
Required=No
Allow Zero Length= Yes
Indexed= Yes (No Duplicates)
Unicode Compression= No
IME Mode= No Control
IME Sentence Mode= None

I created a Find Unmatched Query "ImportHRWithoutMatchingDeptQry" with the
wizard having this SQL:

SELECT ImportHR.Dept, DeptTbl.Department
FROM ImportHR LEFT JOIN DeptTbl ON ImportHR.Dept = DeptTbl.Department
WHERE (((DeptTbl.Department) Is Null));

The append query has this SQL:

INSERT INTO DeptTbl ( Department )
SELECT ImportHRWithoutMatchingDeptQry.Dept
FROM ImportHRWithoutMatchingDeptQry;

When I run the append query simply as a Select query it returns the
appropriate values that should be appended, but when the append query is run
I get the message that no records were appended due to Key Violations. I
can't see any difference between the working HRStatus append query and the
not working Dept append query. Any thoughts?

Thanks
Click to expand...
 
G

Geezer via AccessMonster.com

mscertified said:
This message means you have duplicate keys or indexes. You need to check the
DATA to make sure it does not contain duplicate values. Look at all the
columns to see if any contain the properties INDEXED DUPLICATES NOT ALLOWED.

-Dorian
Click to expand...

Thanks Dorian. I haven't included the Pk in any of the append queries so
they're not be duplicated and the specific field I'm trying to append, in the
example I presented Department, the value that should be appending does not
currently exist in the append To table. The only field in the append To
table set for Indexed Duplicates Not Allowed is the PK, which is autonumber
and not a field included in the append query. Am I missing your point?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch in Expression 4
comparing two tables with the same field 10
Append Query - Key violation 1
Unable to append due to key violations 2
Append Query - key violation - no duplicates, records exist in par 4
Append to two different tables 23
Append query key violation 8
AutoNumber Field Not Populating with Append Query 2

Top