Using a query to add new records to tables

[duwardb]

I wil try to make this a brief as possible. I am working on a database to
keep track of student information in an alternative school. To do this I
have designed three tables that keep track of different information. The
three tables are 1)General Student Information, 2)Student Courses and Grades,
3)Student State Testing Information. All three tables have a Student ID
Number, Student Last Name, and Student First Name Field that needs to be the
same. The Student ID Number field must be unique and is related in all three
tables. What I need to accomplish is an update(append?) of the Student
Courses and Grades and the Student State Testing Information when I add new
records to the Student Information Table. I have tried to use two different
append queries to update each of the tables, which works the first time I
enter students into the database(when no records exist), but when I try to
enter new records and run the queries it gives me an error and does not add
the new records to the other two tables. Could someone help me out here?

 

[Chaim]

As an observation, if the Student ID number uniquely identifies each
student, why is it necessary to repeat the first and last name fields in
each table?

Something sounds wrong with the design. This design flaw may be the real
cause of your update problems.

 

[duwardb]

Chaim,

I tried your suggestion and made sure the First Name and Last Name fields
were not repeated in the grades and testing table. Only the ID Number is in
all three tables. I still get the same error when trying to add new records
to the grades and testing tables when there are existing records in the
tables. The append query works great the first time through, but the next
time gives the following error:

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

Microsoft Office Acces set 0 field(s) to Null due to a type conversion
failure, and it didn't add 2 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.

Again, all I want to do is add new records for new students to the grades
and testing tables. To do this do I need to do two queries? One to find
which records in the Student Information Table is correct and one to add
(append or update?) only the new records to the grades and testing tables.