Append query to check for existing values and add only those not present

[isaacski]

Hi All,
I'm stumped. I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.

Bottom line:
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.

I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do... any advice or help would be appreciated!!

Thanks,
K

 

[isaacski]

UPDATE:
INSERT INTO tblassignment ( AnalystassignmentID )
SELECT refanalyst.AnalystID
FROM refanalyst LEFT JOIN tblassignment ON refanalyst.AnalystID = tblassignment.AnalystassignmentID
WHERE (((tblassignment.AnalystassignmentID) Is Null));

does the trick. It was giving me errors originally because it won't add the record currently being added unless the record is saved first. So on the form for the list of teachers, there is a save button that is coded to save the current record, and then execute the append query. This seems to have done the job