Append Query Not Working


B

Bill Beshlian

I have an append query that has worked in the past but now when I run it I
get the long error message,"Microsoft Access can't append all the records in
the append query..." I have checked everything and cannot find the problem.
Since I cannot attach the screen shot of the error message, here is the SQL
code for the query:
INSERT INTO sick_history ( emp_no )
SELECT ee.emp_no
FROM ee LEFT JOIN sick_history ON ee.emp_no = sick_history.emp_no
WHERE (((sick_history.emp_no) Is Null));

Right now I need to add 80 records to the sick_history table and cannot due
to this error.
TIA, Bill Beshlian
 
Ad

Advertisements

J

John Spencer

Are there any required fields in Sick_History?

Does Sick_History have any relationships to other tables (besides ee) that
won't allow null values in the relating field in Sick_History? For
instance, if Sick_History has relationship that where ee.Emp_no is a foreign
key to another table such as employees table and the relationship is
enforced, you can't add an emp_no to Sick_History unless there is a
corresponding record in the employees table.

Can you open up the Sick_History table in DataSheet view and just add a new
Emp_no and NO OTHER FIELDS? IF not, then you will need to track down which
fields are causing the problem and either modify the fields or modify your
update query.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

Jerry Whittle

If sick_history.emp_no is a primary key field or unique index, the ee table
might have two new records that it's trying to append to the table. Make a
backup and try the following:

INSERT INTO sick_history ( emp_no )
SELECT DISTINCT ee.emp_no
FROM ee
WHERE ee.emp_no Not In (Select emp_no from sick_history) ;

If emp_no is not a PK or unique field and you want multiple entries from ee,
remove the DISTINCT.
 
Ad

Advertisements

J

J_Goddard via AccessMonster.com

Hi -

The "long error message" will indicate which type of problem you are having -
at least one of the counts will be non-zero. please post more detail about
the error.

John


Bill said:
I have an append query that has worked in the past but now when I run it I
get the long error message,"Microsoft Access can't append all the records in
the append query..." I have checked everything and cannot find the problem.
Since I cannot attach the screen shot of the error message, here is the SQL
code for the query:
INSERT INTO sick_history ( emp_no )
SELECT ee.emp_no
FROM ee LEFT JOIN sick_history ON ee.emp_no = sick_history.emp_no
WHERE (((sick_history.emp_no) Is Null));

Right now I need to add 80 records to the sick_history table and cannot due
to this error.
TIA, Bill Beshlian

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top