Help with an append queries

[Ann]

HELP!!!!!
I have two tables that the fields are identical. Only that
one of the tables is a tempory table. This tempory table
is where all my information is entered. This is a student
information table that has the students and ss#, dob,
course # & name. ect. I print reports and certificates
from this table. Once I am finished with my reports..... I
want to copy all the students (meeting a criteria 'course
#') over to the master student table. when I go to run the
append query I get several violation errors, key violation
errors, lock violation errors, rule violation errors. Even
thought I get the errors my records still copy over to the
master table. I dont know what to look for. Then I run a
delete query to delete the appended records from the
tempory table.
***Some of the info will be duplicates because several
students will take courses as needed so Name, dob ss# will
be the same in the Master table(this table history
listing).
I am not sure why I am getting the errors.
Thanks,
Ann

 

[John Vinson]

HELP!!!!!
I have two tables that the fields are identical. Only that
one of the tables is a tempory table. This tempory table
is where all my information is entered. This is a student
information table that has the students and ss#, dob,
course # & name. ect.

Hrm. This table SHOULD NOT EXIST in this form. You're mixing fields
from the Student entity and the Course entity. Are you making the
common incorrect assumption that you must have data all in one table
to generate a report? Well, *you don't*; you can base a Report on a
query joining the Student table (where you have entered the student's
biographical information once and once only), the Courses table (where
you have entered the course name and other course information, again
once only), and an Enrollment table linking the two.

I print reports and certificates
from this table. Once I am finished with my reports..... I
want to copy all the students (meeting a criteria 'course
#') over to the master student table. when I go to run the
append query I get several violation errors, key violation
errors, lock violation errors, rule violation errors.

If the student information already exists in the master table *you
don't need to copy it in again*. In fact you shouldn't be reentering
it in the first place!

Even
thought I get the errors my records still copy over to the
master table. I dont know what to look for. Then I run a
delete query to delete the appended records from the
tempory table.
***Some of the info will be duplicates because several
students will take courses as needed so Name, dob ss# will
be the same in the Master table(this table history
listing).
I am not sure why I am getting the error.

Because you're trying to store the same data twice in the same table.

 

[Richard Hollenbeck]

I'm building a similar database and find that the one-to-many relationships
almost form a complete circle across six tables. The reason they don't
cause a problem is that two of the six tables have composite keys which
keeps the one-to-many relationships from all flowing clockwise or
counterclockwise (or in a complete circle.) So one table relates to the
next, etc., etc. But sometimes I need information from a table on the other
side of the circle. There is no direct relationship between the two tables
I'm trying to get information from. After much mental gymnastics I finally
settled on a temporary table that contains all the fields joining the two
tables and using a series of UPDATE queries in a module, I'm able to get the
data I need into the temporary table. Then I update another table with the
data in the temp table, then, at the end of the procedure, I have a "DELETE
[temp].* from [tblTemp]" query in there to empty the temporary table for
next time. I know it's a table that contains redundant information, but
it's only temporary. The permanant data is still untouched in the original
tables. What's wrong with that?