Using NOT EXISTS In An INSERT Procedure

I am using the following code to insert records into a destination table
that has a three column primary key i.e. (PupilID, TermID &
SubjectGroup). The source table records all the pupils in a school with
(amongst other things) a column (about 50) for each subject the pupil
might potentially sit. In these columns are recorded the study group
that they belong to for those subjects. The destination table holds a
record per pupil per subject per term, against which the teacher will
ultimately record the pupils performance.

The code as shown runs perfectly until the operator tries to insert a
selection of records that include some that already exist. What I would
like it to do is, record those, which do not exist and discard the
remainder. However, whenever a single duplicate occurs SQL rejects the
whole batch. I know that my solution will probably involve using the
‘NOT EXISTS’ expression, but try as I might I cannot get it to work. To
further complicate things, the code is being run from within VBA using
the RunSQL command.

The variables ‘strFieldName’, ‘strGroup’ & ‘strTerm are declared at the
start of the procedure and originate from options selected on an Access
form.

INSERT INTO dbo.yInterimReportData (PupilID, LastName, FirstName,
TermID, SubjectGroup) SELECT PupilID, LastName, FirstName," & "'" &
strTerm & "'" & "," & "'" & strGroup & "'" & "FROM dbo.Pupils WHERE (" &
strFieldName & " = " & "'" & strGroup & "')

Any Ideas?


Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***