Error in Append Query.

[Guest]

Hi,
I have the this code.
INSERT INTO NEW_test ( ADH_01 )
SELECT [ADH-HIV].ADH_01
FROM [ADH-HIV] INNER JOIN [ADH-OTH] ON [ADH-HIV].STUDYID = [ADH-OTH].STUDYID;
When run the query gives me the following error.
Microsoft Access cannot append all the records in the append query.
Microsoft access set 0 fields to Null due to a type conversion failiur, and
it did'nt add 44 records to the table due to key violations,0 records due to
lock violations and 0,records due to validation rule violations.
Can you tell me what could be the problem.
Thanks.

 

[Allen Browne]

Are there other fields in table NEW_test than just the ADH_01 field?

I'm guessing there is at least one other field, that it is a Number field, a
foreign key to another table. If so, open the table in design view, and
remove the 0 from the field's Default Property. Since your query does not
assign a value to this field, Access tries to assign the default value of
zero that it always sets up for numeric fields, and of course that is a key
violation (assuming the table it relates to does not have a zero in its
AutoNumber field.)

 

[Guest]

Yes,I have 3 more fields.I did remove the 0 from the default property of all
the field.But it is still giving me the same error.

Are there other fields in table NEW_test than just the ADH_01 field?

I'm guessing there is at least one other field, that it is a Number field, a
foreign key to another table. If so, open the table in design view, and
remove the 0 from the field's Default Property. Since your query does not
assign a value to this field, Access tries to assign the default value of
zero that it always sets up for numeric fields, and of course that is a key
violation (assuming the table it relates to does not have a zero in its
AutoNumber field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have the this code.
INSERT INTO NEW_test ( ADH_01 )
SELECT [ADH-HIV].ADH_01
FROM [ADH-HIV] INNER JOIN [ADH-OTH] ON [ADH-HIV].STUDYID =
[ADH-OTH].STUDYID;
When run the query gives me the following error.
Microsoft Access cannot append all the records in the append query.
Microsoft access set 0 fields to Null due to a type conversion failiur,
and
it did'nt add 44 records to the table due to key violations,0 records due
to
lock violations and 0,records due to validation rule violations.
Can you tell me what could be the problem.
Thanks.

 

[Allen Browne]

The "key violations" error means Access is trying to assign a value to a
field that is a foreign key to another table, and the value does not match
any record in the other table.

Try to figure out which field this could be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes,I have 3 more fields.I did remove the 0 from the default property of
all
the field.But it is still giving me the same error.

Are there other fields in table NEW_test than just the ADH_01 field?

I'm guessing there is at least one other field, that it is a Number
field, a
foreign key to another table. If so, open the table in design view, and
remove the 0 from the field's Default Property. Since your query does not
assign a value to this field, Access tries to assign the default value of
zero that it always sets up for numeric fields, and of course that is a
key
violation (assuming the table it relates to does not have a zero in its
AutoNumber field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have the this code.
INSERT INTO NEW_test ( ADH_01 )
SELECT [ADH-HIV].ADH_01
FROM [ADH-HIV] INNER JOIN [ADH-OTH] ON [ADH-HIV].STUDYID =
[ADH-OTH].STUDYID;
When run the query gives me the following error.
Microsoft Access cannot append all the records in the append query.
Microsoft access set 0 fields to Null due to a type conversion failiur,
and
it did'nt add 44 records to the table due to key violations,0 records
due
to
lock violations and 0,records due to validation rule violations.
Can you tell me what could be the problem.
Thanks.

 

[Guest]

Hi Niki,

In addition to Allen's suggestion, there is also the possibility that these
values already exist in the target table and that would violate the primary
key or other unique index to add them to the table. Have you checked to see
if the values already exist in the table (I know this is obvious, but
sometimes those are the easiest things to overlook).

-Ted Allen

Yes,I have 3 more fields.I did remove the 0 from the default property of all
the field.But it is still giving me the same error.

Are there other fields in table NEW_test than just the ADH_01 field?

I'm guessing there is at least one other field, that it is a Number field, a
foreign key to another table. If so, open the table in design view, and
remove the 0 from the field's Default Property. Since your query does not
assign a value to this field, Access tries to assign the default value of
zero that it always sets up for numeric fields, and of course that is a key
violation (assuming the table it relates to does not have a zero in its
AutoNumber field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have the this code.
INSERT INTO NEW_test ( ADH_01 )
SELECT [ADH-HIV].ADH_01
FROM [ADH-HIV] INNER JOIN [ADH-OTH] ON [ADH-HIV].STUDYID =
[ADH-OTH].STUDYID;
When run the query gives me the following error.
Microsoft Access cannot append all the records in the append query.
Microsoft access set 0 fields to Null due to a type conversion failiur,
and
it did'nt add 44 records to the table due to key violations,0 records due
to
lock violations and 0,records due to validation rule violations.
Can you tell me what could be the problem.
Thanks.

 

[Guest]

Hi,

What I am trying is.I have created a new table(NEW_test) with the same
primary key(STUDY_ID) data as in an existing table (ADH_HIV whose primary key
is STUDYID...I made sure I am giving different names to avoid problems.).

I want to move all the fields in ADH_HIV to NEW_test with different field
names.I tried to update first but it gave me the same error.So I tried
appending but again the same error.
I am not sure what the problem is.I tried what Allen said,and renamed the
fields also to avoid confusion.
Let me know if there is anything else that could be done.
Thanks.

Hi Niki,

In addition to Allen's suggestion, there is also the possibility that these
values already exist in the target table and that would violate the primary
key or other unique index to add them to the table. Have you checked to see
if the values already exist in the table (I know this is obvious, but
sometimes those are the easiest things to overlook).

-Ted Allen

Yes,I have 3 more fields.I did remove the 0 from the default property of all
the field.But it is still giving me the same error.

Are there other fields in table NEW_test than just the ADH_01 field?

I'm guessing there is at least one other field, that it is a Number field, a
foreign key to another table. If so, open the table in design view, and
remove the 0 from the field's Default Property. Since your query does not
assign a value to this field, Access tries to assign the default value of
zero that it always sets up for numeric fields, and of course that is a key
violation (assuming the table it relates to does not have a zero in its
AutoNumber field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have the this code.
INSERT INTO NEW_test ( ADH_01 )
SELECT [ADH-HIV].ADH_01
FROM [ADH-HIV] INNER JOIN [ADH-OTH] ON [ADH-HIV].STUDYID =
[ADH-OTH].STUDYID;
When run the query gives me the following error.
Microsoft Access cannot append all the records in the append query.
Microsoft access set 0 fields to Null due to a type conversion failiur,
and
it did'nt add 44 records to the table due to key violations,0 records due
to
lock violations and 0,records due to validation rule violations.
Can you tell me what could be the problem.
Thanks.

 

[Guest]

Does the NEW_test table already contain records for each of the STUDY_ID's?
If so, you will need to use an update query to update the other fields to
match your source table. If not, you need to modify your append query to
append the StudyID value as well as all of the other field values at the same
time. Your original append query syntax was only appending one field value
(ADH_01), which would mean that the Study_ID value would be blank for all,
which is not allowed if that is the primary key field.

-Ted Allen

Hi,

What I am trying is.I have created a new table(NEW_test) with the same
primary key(STUDY_ID) data as in an existing table (ADH_HIV whose primary key
is STUDYID...I made sure I am giving different names to avoid problems.).

I want to move all the fields in ADH_HIV to NEW_test with different field
names.I tried to update first but it gave me the same error.So I tried
appending but again the same error.
I am not sure what the problem is.I tried what Allen said,and renamed the
fields also to avoid confusion.
Let me know if there is anything else that could be done.
Thanks.

Hi Niki,

In addition to Allen's suggestion, there is also the possibility that these
values already exist in the target table and that would violate the primary
key or other unique index to add them to the table. Have you checked to see
if the values already exist in the table (I know this is obvious, but
sometimes those are the easiest things to overlook).

-Ted Allen

Yes,I have 3 more fields.I did remove the 0 from the default property of all
the field.But it is still giving me the same error.

:

Are there other fields in table NEW_test than just the ADH_01 field?

I'm guessing there is at least one other field, that it is a Number field, a
foreign key to another table. If so, open the table in design view, and
remove the 0 from the field's Default Property. Since your query does not
assign a value to this field, Access tries to assign the default value of
zero that it always sets up for numeric fields, and of course that is a key
violation (assuming the table it relates to does not have a zero in its
AutoNumber field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have the this code.
INSERT INTO NEW_test ( ADH_01 )
SELECT [ADH-HIV].ADH_01
FROM [ADH-HIV] INNER JOIN [ADH-OTH] ON [ADH-HIV].STUDYID =
[ADH-OTH].STUDYID;
When run the query gives me the following error.
Microsoft Access cannot append all the records in the append query.
Microsoft access set 0 fields to Null due to a type conversion failiur,
and
it did'nt add 44 records to the table due to key violations,0 records due
to
lock violations and 0,records due to validation rule violations.
Can you tell me what could be the problem.
Thanks.