Add multiple students for a single course

[Guest]

This may have been answered before, but I have not found where so please
forgive me if this has already been answered elsewhere.

I have a students table with all the "person" data, a class/course table
with the course name, Course Number and all course related information.

What I am trying to do is have a single form that will let you enter the
class info one time (courses are selected from a drop down) but let you add
mulitple students from the same form.

Can this be done?

 

[Douglas J Steele]

One possibility is the Select Records Sandra Daigle has near the bottom of
the page at http://www.daiglenet.com/MSAccess.htm

 

[Jeff Boyce]

One approach to this would be a form with a combo box (drop-down) for the
courses, and a pair of listboxes, one for "available" students and the other
for "enrolled" students.

If you've used any of the wizards in Access, you've probably seen the
"matched listbox" approach, in which the left listbox lists "available"
somethings, 4 buttons let you move somethings back/forth, and the right
listbox lists "chosen" somethings.

You'd need to write the code behind the buttons to move one/all of the
students either to or from the "enrolled" listbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Linson]

This may have been answered before, but I have not found where so please
forgive me if this has already been answered elsewhere.

I have a students table with all the "person" data, a class/course table
with the course name, Course Number and all course related information.

What I am trying to do is have a single form that will let you enter the
class info one time (courses are selected from a drop down) but let you
add
mulitple students from the same form.

Very likely you will need two more tables, and it should not be difficult.

You need to distinguished between Course and instances of that Course
(classes, identified also by date/time/teacher). Then you will need a
Student-Class "intersection table" showing which students are in which
class.

You could then implement a Class form, where you enter the information that
distinguishes one instance of a Course from another instance, with a Subform
Control in which you embed a continuous-view Form that picks up the Class
identification from the main Form and allows you to select a Student from
the Students Table using a Combo Box.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,
Thank you, I did the continuous-view form. That allowed for a new entry, but
it did not keep all the "course info" on the next form. How do I get it to
keep the course information for each new student?

 

[Larry Linson]

Thank you, I did the continuous-view form.
That allowed for a new entry, but it did not
keep all the "course info" on the next form.
How do I get it to keep the course information
for each new student?

"All the course info" is a bit troubling to me, indicating that you may be
storing redundant data.

I suggested the following tables:

(1) Course Table -- with information about the Courses themselves

(2) Class Table -- instances of a Course, with a Foreign Key to
Course Table, and Day/Date of Class to identify the instance

(3) Student Table -- information about the Students themselves

(4) Student-Class Table -- showing what students are enrolled in
what class, possibly needing nothing more than Foreign Keys
to the Class Table and the Student Table.

The main Form I mentioned should show, in single-form view, records from a
Query with data sources of the Class Table (about the instance of the
Course) and Course Table (descriptive information about the Course, not
editable in this form). It should contain a Subform Control containing a
continuous-forms view of records from a Query with data sources of the
Student-Class Table (to identify the students registered) and Student Table
(descriptive information about the Student, if needed, but not editable in
this form).

It's possible that if you allow selection of the Student in a Combo Box with
a Row Source including key, name, and perhaps other information from the
Student Table, you may not need to include the Student Table in the Query
for the Form embedded in the Subform Control, and just store the Student
Table key as the Foreign Key to the Student Table.

I'd suggest some code behind the scenes, to verify that you haven't already
enrolled this student in this class, or course, or in another course at the
same day/time period, too.

Larry Linson
Microsoft Access MVP

The Course information should be accessed via the Foreign key in the Class
(instance of a course) Table, which should be the table displayed in the
main form, with the student-enrolled information in the continuous forms
view Form that is displayed in the Subform Control.

 

[Guest]

Larry,
I already have the tables (as listed below)
Courses Table: which contains the course information
Class Table: which contains the class specific information
Student Table: which contains the students information
Training table: which student took what class

There are only 50 students (which will never change)
There are only 58 Courses that are being taught (never changes)

The only things that change for the Course is the length of the class, what
they did in the class, the date and the instructor.

So what I have done is created a form that has the Course title as a drop
down list (so they can't mess it up), there are several check boxes for other
class information that they select based on what the class was, then they can
enter the lenght of class, date and time of class. All the other "Course"
information is already there for them. All this works fine.

I want to be able to just add a list of (maybe 10) students at the same time
for the one course, instead of having to do a new form for each student that
is taking the same course.

Does that make sense?

Thank you.