SQL to add records from table to another, with ability to add the same source records again-CodePudding

I have two tables, students and classes. I built a form with two listboxes on it, one for the available students, and the other for students in a selected class (using multiple dependent, unbound, comboboxes as filters).
I'm working on moving the clicked item from the available students list to the selected class list (thus adding a record to the classes table.

All is working fine, except that my SQL expression below is moving the clicked item from the first list once and for all, preventing me from adding the same student to another class. I need the name of the student to reappear each time I change the class (round in the code). How can I achieve this?

lstNonRelatedStudents.RowSource = "SELECT sdtFullName, tbl_sdt_Info.sdtID " & _
   " FROM tbl_sdt_Info " & _
   " LEFT join tbl_sdt_Rounds ON tbl_sdt_Info.sdtID = tbl_sdt_Rounds.sdtID " & _
   " WHERE ((tbl_sdt_Rounds.sdtID) Is Null) "

CodePudding user response：

This appears to be a many-to-many relationship which means there should be 3 tables like: Students, Classes, StudentClasses.

Current SQL statement selects students based on whether or not their ID is already in StudentClasses (Rounds) table for any class. Now the selection of students must be dependent on whether or not they have already been associated with specified class. Consider something like:

SELECT sdtID, sdtFullName FROM Students 
WHERE NOT sdtID IN (SELECT sdtID_FK FROM StudentClasses WHERE classID_FK = [cbxClass]);