Problem in append query in MS ACCESS sql

Question

I am using MS Access. I have written this query...

INSERT INTO survey1 ( [Coach No] )
SELECT pvc1.[Coach No]
FROM pvc1 LEFT JOIN survey1 ON pvc1.[Coach No]=survey1.[Coach No]
WHERE (((survey1.[Coach No]) Is Null));

BUT it is not appending data in my table survey1...

while running query it is saying that it can't append due to KEY VIOLATIONS. But I am not able to figure out which key violations? — Sunshine
– Sunshine, Commented Aug 3, 2011 at 11:27

Al W · Accepted Answer · 2011-08-02 17:21:27Z

2

Break the query up. Does just the select return any results?

SELECT pvc1.[Coach No]
FROM pvc1 LEFT JOIN survey1 ON pvc1.[Coach No]=survey1.[Coach No]
WHERE (((survey1.[Coach No]) Is Null))

answered Aug 2, 2011 at 17:21

Al W

7,7611 gold badge21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jacob · Accepted Answer · 2011-08-03 16:45:16Z

1

Your query doesn't make sense. You are joining on NULL, then you try to insert that NULL into a table as a PK where it originally came from. You are joining with survey1 on Coach No and are trying to insert the Coach No back into survey1 What are you trying to do here?

Update now that OP elaborated on what he wants to do:

INSERT INTO survey1 ( [Coach No] )
SELECT pvc1.[Coach No]
FROM pvc1 
WHERE pvc1.[Coach No] NOT IN (SELECT [Coach No] FROM survey1 WHERE NOT [Coach No] IS NULL)

edited Aug 3, 2011 at 16:45

answered Aug 3, 2011 at 11:45

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

5 Comments

Sunshine Over a year ago

Actually I want to make a query to add [Coach No] from [pvc1] that are not in [survey1].

Sunshine Over a year ago

It is selecting those [Coach No] in datasheet view but do not append it when I run it. When it is run it gives error message like it can't append due to some KEY VIOLATIONS.

Jacob Over a year ago

Ok, is this a foreign key tzo some other table?

Sunshine Over a year ago

[Coach No] is a primary key in both the tables [pvc1] and [survey1].

Jacob Over a year ago

And it's not a foreign key to some other PK?

Collectives™ on Stack Overflow

Problem in append query in MS ACCESS sql

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related