seeking efficient way to insert many linking records

J

Joe Clark

This is kind of a SQL question. I have a teaching database of
students and classes, and I've made a feature that lets the teacher
(me) look at the whole database and select the students to add to his
class. The reason is that I often get my class roster as an Excel
file and it's easier to Import them all to the table, then attach them
to the class. This is a many-to-many link with a Class_Student table
whose dual primary key is "classID" and "studentID".

I've got a pop-up form that shows the roster, I've added a "selected"
checkbox field to each Student record, and have worked up "check all",
"uncheck selection" and so on. Now I want to create the "Add to the
class" button and I'm not sure how to do it.

One way I suppose is to loop through the list of students and, if the
student is "checked", run a SELECT query to find out if there's
already a linking record in Class_Student, then run an INSERT query to
Class_Student. That seems like a lot of SQL queries.

An alternative way might be to simply not -show- the students that are
already -in- the class, then I wouldn't have to check each one for a
linking record. But how do I create such a query? It has to list all
the Students NOT linked to the Class, even those linked to some other
class, and only show each one once. My SQL skill is not up to it.
 
D

Douglas J. Steele

Are you actually updating (temporarily) a Selected field in the Student
table? If so, to add students to class ID 5, you'd use SQL like:

INSERT INTO Class_Student
SELECT studentID, 5
FROM Student
WHERE Selected = True
AND studentID NOT IN (SELECT studentID FROM Class_Student WHERE classID = 5)
 
M

Michel Walsh

If you have a list of all the students, in addition to your list of
Class_Student, then it is a matter to run the query about finding students
having no match in class_student:




----------------------------------------------
SELECT students.*

FROM students LEFT JOIN ( SELECT studentID
FROM Class_Student
WHERE ClassID= 222) AS a
ON students.studentID = a.studentID

WHERE a.studentID IS NULL
----------------------------------------------




(for classID = 222, replace the 222 by the required value)



Hoping it may help,
Vanderghast, Access MVP
 
J

Joe Clark

Ah, thanks for both replies. I wasn't aware of the "NOT IN (SELECT)"
grammar, and I never thought of using a WHERE in the LEFT JOIN. I'll
try one of these in the morning (it's now 12:32am in Beijing).
 
M

Michel Walsh

.... and Joe Clark is the name of a former Canadian Prime Minister :)


Vanderghast, Access MVP
 
J

Joe Clark

... and Joe Clark is the name of a former Canadian Prime Minister :)

Yeah, I have a "Canadian Detector" test. Basically at a party I
introduce myself. If the person's eyes widen or a laugh escapes,
they're Canadian.
 
J

Joe Clark

----------------------------------------------
SELECT students.*
FROM students LEFT JOIN ( SELECT studentID
FROM Class_Student
WHERE ClassID= 222) AS a
ON students.studentID = a.studentID
WHERE a.studentID IS NULL
----------------------------------------------

I did this and it works, but I have another newbie question: Instead
of "222" I put a variable into the SQL statement, let's call it [x].
How can I set this variable dynamically when the form opens? Note,
this is a subform and it must get the variable from a parent form
which is itself opened by a grand-parent form. I think I understand
how to pass the variable in VBA, but not how to insert it to the SQL
statement before/during the query.
 
F

Fred Boer

Joe who? <g>

There is an NHL hockey player currently playing named "Paul Martin";
whenever my son hears his name in the play by play he has to snicker at the
image this produces in his mind...

Cheers, eh!
Fred Boer
 
M

Michel Walsh

You cannot pass a variable to Jet, but you can use a VBA function that will
return your VBA variable. In a standard module (not a under class, not under
a form):

----------------------
Public Function ClassIDcte() AS Long
ClassIDcte=myCte
End Function
-----------------------


and use ClassIDcte() in your query. You have to define myCte too, somewhere,
accessible to your function.


Hoping it may help,
Vanderghast, Access MVP


Joe Clark said:
----------------------------------------------
SELECT students.*
FROM students LEFT JOIN ( SELECT studentID
FROM Class_Student
WHERE ClassID= 222) AS a
ON students.studentID = a.studentID
WHERE a.studentID IS NULL
----------------------------------------------

I did this and it works, but I have another newbie question: Instead
of "222" I put a variable into the SQL statement, let's call it [x].
How can I set this variable dynamically when the form opens? Note,
this is a subform and it must get the variable from a parent form
which is itself opened by a grand-parent form. I think I understand
how to pass the variable in VBA, but not how to insert it to the SQL
statement before/during the query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top