Running into error while writing Append Query

[SeekerFan]

I'm trying to write an Append query to copy Employee numbers from one table
to another table. The Source table is left joined to the Destination table.
The QEB is set up as below:
Field: txtEmployeeNumber txtEmplNbr
Table: tblEmployee tblTime&SvcAwds
Sort:
Appendto: txtEmpNbr
Criteria: Is Null

When I try to run the query, I keep getting the following error message:
"Tracker set 0 Field(s) to Null due to a type conversion failure, and it
didn't add 1 record(s) to the table due ot key violations, 0 record(s) due to
lock violations, and 0 record(s) due to validation rule violations."
Thank you,

 

[Jeff Boyce]

Madelyn

If the error message is to be believed (always a bit of a gamble...<g>), one
of the records you are trying to append has a key (primary key or unique
index) value that already exists in the append-to table.

Check your data -- if you change that Append query to a Select query, you
can look over what would be added.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KenSheridan via AccessMonster.com]

Madelyn:

If I understand correctly what you are attempting I think the txtEmpNbr
should be in the 'Append To' row of the txtEmployeeNumber column, not of the
txtEmplNbr column. At present you are attempting to insert a row with a Null
txtEmpNbr. A primary key, or any part of it, cannot be Null.

Another way of doing this would be by means of a subquery to identify those
rows without a match in tblTime&SvcAwds:

INSERT INTO [tblTime&SvcAwds](txtEmplNbr)
SELECT txtEmployeeNumber
FROM tblEmployee
WHERE NOT EXISTS
(SELECT *
FROM [tblTime&SvcAwds]
WHERE [tblTime&SvcAwds].txtEmplNbr = tblEmployee.txtEmployeeNumber);

Ken Sheridan
Stafford, England

 

[SeekerFan]

OK, I fixed that by making a new field in the destination table with an
AutoNumber ID as the Primary Key. When I run it as a Select Query, it shows
me the Employee number, but when I try to do it as an Append Query, it
creates a new AutoNumber ID however it is not copying the employee number
onto the table.