Solved Unable to Filter Insert Query Properly

[Pac-Man]

Hello,

I am using following query to insert values in tblSubjectExam but with condition that if those values do not exist already. SQL is:

INSERT INTO tblSubjectExam ( ClassSubjectID, ExamTerm )
SELECT DISTINCTROW tblClassAndSubjects.ClassSubjectID, [forms]![frmSubjects]![cboExamID] AS expExamTerm
FROM tblClassAndSubjects LEFT JOIN tblSubjectExam ON tblClassAndSubjects.ClassSubjectID = tblSubjectExam.ClassSubjectID
WHERE (((tblClassAndSubjects.ClassID)=[forms]![frmSubjects]![cboClassID]) AND ((tblSubjectExam.ExamTerm)<>[forms]![frmSubjects]![cboExamID]));

When I run the query, it create duplicate records. CboExamID is like First Term, Second Term and Final Term. The form frmSubjects contain combo boxes by which user select Class (ClassID) and Exam Term. Purpose is to create records in tblSubjectExam for specific class and all the subjects registered for that class when term is changed and if records for that are already not present. I have spent my whole day to figure out this but failed. Please help.

Best Regards

 

[Pac-Man]

Try applying an index to prevent duplicates.

Thanks for reply @Uncle Gizmo, actually ClassSubjectID and ExamTerm will be duplicated in individual but for ClassSubjectID 1, there should only be one "first term".

 

[Pac-Man]

I don't really understand your response. However if you have a situation where you have to allow duplicates and can't apply an index, then you have to check to see if the record exists first before adding it.

Thanks for reply. This function may not work because duplicate of records can exist individually. Eg. ClassSubjectID = 1 can exist for First Term Exam, also for Second Term and Final Term. Similarly First Term can exisit for ClassSubjectID =1, 2 upto so on. But ClassSubjectID and ExamTerm can not replicate together i.e. There cannot be two records in which ClassSubjectID = 1 and ExamTerm = First Term.

 

[Pac-Man]

I successfully achieved the purpose using NOT EXISTS in where clause of SQL. Thanks for your replies.