What am I doing wrong here?

[Christian Blackburn]

Hi Gang,
I am trying to do something very simple in my mind anyways . I would like
to copy the Name and Web fields from a Jobs table to an Employers table.
Both tables have the same Name and Web constraints. The tables have an ID
in common Jobs.Company_ID = Employers.ID. And here's the non functional
SQL:
INSERT INTO Jobs (Name, Web )
SELECT [Employers].[Name], [Employers].[Web]
FROM Employers, Jobs
WHERE [Jobs].[Company_ID]=[Employers].[ID];

Thanks for any and all help regarding this matter,
Christian Blackburn

 

[Ken Snell]

Nonfunctional as in...gives an error message? gives the wrong result?

 

[Christian Blackburn]

Hi Ken,
I'm sorry as you can see the error message was very long winded and I simply
didn't want to type it up, but you're right it's certainly needed:
[Microsoft Access]
Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 11 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help
[Yes] [No] [Help]

Thanks for your help,
Christian Blackburn

Nonfunctional as in...gives an error message? gives the wrong result?
--

Ken Snell
<MS ACCESS MVP>

Hi Gang,
I am trying to do something very simple in my mind anyways . I would like
to copy the Name and Web fields from a Jobs table to an Employers table.
Both tables have the same Name and Web constraints. The tables have an ID
in common Jobs.Company_ID = Employers.ID. And here's the non functional
SQL:
INSERT INTO Jobs (Name, Web )
SELECT [Employers].[Name], [Employers].[Web]
FROM Employers, Jobs
WHERE [Jobs].[Company_ID]=[Employers].[ID];

Thanks for any and all help regarding this matter,
Christian Blackburn

 

[Ken Snell]

This error message means that your query is trying to add records that would
create a duplicate primary key field value. Either (or both) Name or Web is
part of the primary key index, and 11 records have values that already exist
in the Job table.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
I'm sorry as you can see the error message was very long winded and I simply
didn't want to type it up, but you're right it's certainly needed:
[Microsoft Access]
Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 11 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help
[Yes] [No] [Help]

Thanks for your help,
Christian Blackburn

Nonfunctional as in...gives an error message? gives the wrong result?
--

Ken Snell
<MS ACCESS MVP>

message news:[email protected]... would
like

an

ID

in common Jobs.Company_ID = Employers.ID. And here's the non functional
SQL:
INSERT INTO Jobs (Name, Web )
SELECT [Employers].[Name], [Employers].[Web]
FROM Employers, Jobs
WHERE [Jobs].[Company_ID]=[Employers].[ID];

Thanks for any and all help regarding this matter,
Christian Blackburn

 

[[MVP] S.Clark]

Examine your SQL string again. What fields are those VALUES being stuffed
into? Assume nothing.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I am having the same sort of problem. I am very new to Access and SQL.
I need to use the SQL view to do a simple INSERT (it's for a student
project):

INSERT INTO EMPLOYEE
VALUES ('Joseph','Norton','9194530394','70990238',1);

My question: - What is meant by "primary key violation"? This is the
first data I am attempting to insert into my database, so this error
message is confusing to me. Any ideas what I could be doing wrong?

Here's my create statement:
CREATE TABLE EMPLOYEE
(
[FNAME] VARCHAR(15) NOT NULL,
[LNAME] VARCHAR(15) NOT NULL,
[PHONE] VARCHAR(10) NOT NULL,
[DL] VARCHAR(12) NOT NULL,
[DNO] INT NOT NULL,
CONSTRAINT EMPPK PRIMARY KEY(DL),
CONSTRAINT EMPFK FOREIGN KEY(DNO) REFERENCES DEPT(DNUMBER)
);

Here is the error message I get:

[Microsoft Access]
Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion
failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation
rule
violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help
[Yes] [No] [Help]

Hi Ken,
I'm sorry as you can see the error message was very long winded and I
simply
didn't want to type it up, but you're right it's certainly needed:
[Microsoft Access]
Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion
failure,
and it didn't add 11 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation
rule
violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help
[Yes] [No] [Help]

Thanks for your help,
Christian Blackburn


"Ken Snell" (e-mail address removed) wrote in message
Nonfunctional as in...gives an error message? gives the wrong result?
--

Ken Snell
MS ACCESS MVP

"Christian Blackburn" (e-mail address removed)@Hotmail.com wrote
in
message Hi Gang,
I am trying to do something very simple in my mind anyways . I
would
liketo copy the Name and Web fields from a Jobs table to an Employers
table.
Both tables have the same Name and Web constraints. The tables have
an
ID
in common Jobs.Company_ID = Employers.ID. And here's the non
functional
SQL:
INSERT INTO Jobs (Name, Web )
SELECT [Employers].[Name], [Employers].[Web]
FROM Employers, Jobs
WHERE [Jobs].[Company_ID]=[Employers].[ID];

Thanks for any and all help regarding this matter,
Christian Blackburn

 

[John Vinson]

I am having the same sort of problem. I am very new to Access and SQL.
I need to use the SQL view to do a simple INSERT (it's for a student
project):

INSERT INTO EMPLOYEE
VALUES ('Joseph','Norton','9194530394','70990238',1);

My question: - What is meant by "primary key violation"?

A Primary Key is by definition unique. If you try to insert two
records in the table both with 70990238 in the DL field - your Primary
Key - you'll get this error message. Could you perhaps be running the
query twice?

John W. Vinson[MVP]

 

[Chris2]

I am having the same sort of problem. I am very new to Access and SQL.
I need to use the SQL view to do a simple INSERT (it's for a student
project):

INSERT INTO EMPLOYEE
VALUES ('Joseph','Norton','9194530394','70990238',1);

My question: - What is meant by "primary key violation"? This is the
first data I am attempting to insert into my database, so this error
message is confusing to me. Any ideas what I could be doing wrong?

Here's my create statement:
CREATE TABLE EMPLOYEE
(
[FNAME] VARCHAR(15) NOT NULL,
[LNAME] VARCHAR(15) NOT NULL,
[PHONE] VARCHAR(10) NOT NULL,
[DL] VARCHAR(12) NOT NULL,
[DNO] INT NOT NULL,
CONSTRAINT EMPPK PRIMARY KEY(DL),
CONSTRAINT EMPFK FOREIGN KEY(DNO) REFERENCES DEPT(DNUMBER)
);

Here is the error message I get:

[Microsoft Access]
Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion
failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation
rule violations.

My best guess: The key violation was that the Access found that the value
1, on INSERT into EMPLOYEE.DNO, wasn't also in DEPT.DNUMBER.

(Unnecessary Commentary: BTW, you have two attributes in your database,
DNO and DNUMBER, that are apparently the same thing . . . because of this,
they should also have the same name.)

Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help
[Yes] [No] [Help]