Solved Unable to Filter Insert Query Properly (1 Viewer)

Pac-Man

Active member
Local time
Today, 14:54
Joined
Apr 14, 2020
Messages
408
Hello,

I am using following query to insert values in tblSubjectExam but with condition that if those values do not exist already. SQL is:
SQL:
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:54
Joined
Jul 9, 2003
Messages
16,245
When I run the query, it create duplicate records.
Try applying an index to prevent duplicates.

See my Blog here:-

 

Pac-Man

Active member
Local time
Today, 14:54
Joined
Apr 14, 2020
Messages
408
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".
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:54
Joined
Jul 9, 2003
Messages
16,245
ClassSubjectID and ExamTerm will be duplicated

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.

I have used this Microsoft Code to count the number of records in a recordset which you feed in through the function parameter:-

Count the number of records in a DAO Recordset​

 
Last edited:

Pac-Man

Active member
Local time
Today, 14:54
Joined
Apr 14, 2020
Messages
408
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:54
Joined
Jul 9, 2003
Messages
16,245
This function may not work because duplicate of records can exist individually

Like I said I don't really understand your problem, and I can't get my head around your explanation. I suggest you post some data and show the data as it is now, and show the data that you want. Point out the differences and hopefully this will make it clear what you want.
 

Pac-Man

Active member
Local time
Today, 14:54
Joined
Apr 14, 2020
Messages
408
I successfully achieved the purpose using NOT EXISTS in where clause of SQL. Thanks for your replies.
 

Users who are viewing this thread

Top Bottom