Error runnign append query

[istone]

Hi,
An append query is giving me the following error:

qryPar can't append all the records in the append query
qryPar set 0 field(s) to null due to at type conversion failure, and it did not add 4626 records due to key violations, 0 records to lock violations, and 0 records due to validation rule violations.
Do you run the query....etc?

I could not see where the problem is. Please let me know if you have an answer. Thanks in advance.

Here is the query code:

INSERT INTO tblPolicies ( PolicyNumber, CarrierNumber, Benefit, ClientNumber )
SELECT tblClientPolicies.PolicyNumber, tblClientPolicies.CarrierNumber, tblClientPolicies.BenefitType, tblClientPolicies.ClientNumber
FROM tblClientPolicies;

Thanks in advance.

 

[Remou]

I imagine you have a unique index and you are trying to add duplicate records or you are trying to add records with no key data to a unique index.

 

[istone]

Yes, I do have duplicate records. What should i do?
Thanks

 

[Remou]

What do you want to do? Replace the existing data with data from the duplicates? Renumber the duplicates? Have you an Autonumber field? A few clues would be nice.

 

[istone]

I would like to Replace the existing data from the duplicates.

CarrierNumber is number
PolicyNumber is text
Benefit is text
ClientNumber is number

RecordNumber is an autonumber but is not used to append to.

Thanks Remou

 

[Remou]

In that case, you need an Update query, not an Append query. Join the two tables on the key field.

 

[PHV]

What about this (APPEND new, UPDATE existing)?
UPDATE tblPolicies AS P RIGHT JOIN tblClientPolicies AS C ON P.PolicyNumber = C.PolicyNumber
SET P.PolicyNumber = C.PolicyNumber, P.CarrierNumber = C.CarrierNumber
, P.Benefit = C.BenefitType, P.ClientNumber = C.ClientNumber;

Assumptions: PolicyNumber is the PrimeryKey of tblPolicies.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886