simplest way...?

[mike]

what's the simplest way to do this?

i have a list of student records. each student has the same fields
(classes). using a form to display each record. want to enable user to
locate student and then select the courses (check boxes) from which i can
generate a report to see the courses selected for each student.

my form has a list box that will display the student name and a check box
for each class. I have a corresponding field for each check box.

For example, when i pull up "Joe Smith" in the list box and i select the
English and Math check box on the form, the English and Match field for Joe
Smith needs to be checked or set to yes. Then i can generate a report to
determine which students were assigned to which class(es) marked yes.

 

[Allen Browne]

Mike, can I encourage you to slow down, and do some reading about how to
create a relational design?

Here's a starting point:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

I have also posted this in another group where you asked a similar question.

 

[mike]

allen,

thx for the tip. i'm doing just that...

Mike, can I encourage you to slow down, and do some reading about how to
create a relational design?

Here's a starting point:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

I have also posted this in another group where you asked a similar question.

 

[mike]

allen,

i'm using relationships now but having trouble w/ interface.

tblStudent: list of students
tblEnglish: list of english sections
tblStudentEnglish: list of studID and EngID 'who's in what section

interface:
-combo in form hdr that displays list of students
-subform for combo box of english sections

how do i tie it all together so tblStudentEnglish is updated with selected
student and english section?

great website by the way.

 

[Allen Browne]

There's quite a bit more to it than that.

Firstly, don't use tblEnglish, tblMath, etc.
Instead, create a table (say tblSubject) with each subject you offer.
It will have a record for English, a record for Math, etc.
Depending on what you are doing, you may actually have records for
English101, English201, etc.

Now, over time, one subject is offered many times, e.g. English101 may be
offerd in semester 1 of 2008, and again in semester 2 of 2009. There is
therefore a one-to-many relationship between the subjects and the instances.
A students does not enrol in a subject, but in an instances of a subject.

If we call the instance of a subject a class, you will have a tblClass with
fields like this:
- ClassID AutoNumber primary key
- SubjectID relates to tblSubject.SubjectID.
- StartDate when this class begins
- SupervisorID the teacher responsible for this class.

Now you can enrol students in a class. tblClassStudent will have fields:
- ClassID relates to tblClass.ClassID
- StudentID relates to tblStudent.StudentID

Now you can interface this with a main form bound to tblClass, and a subform
bound to tblClassStudent. The subform is in continuous form view, so it
shows one student per row, as many rows as you need.

As I mentioned before, there's a great deal more to it if you also need to
track attendance, submissions of assessments, results and so on.

 

[mike]

the purpose of this DB is to allow a teacher to locate a student and then
select the recommended course for that student in each of the subject areas:
Math, English, Science, Soc Studies, and Electives. For exampl, a teacher
selects student Joe Smith from the name cbo and English IV Honors from the
english cbo, which should then write the student id and english id choices to
a new record in the tblStudentEnglish. tblStudentEnglish will contain a
record of each student and the section he/she is recommended for.

i believe this is identical to your example w/ Client and Diseases. I just
cant get my frmStudent to write to tblStudentEnglish the way your frmClient
does for tblClientDiseases.

 

[Allen Browne]

Okay, in that case you can just use:
tblStudent
tblSubject
tblStudentSubject

Interface with a main form bound to tblStudent (since you want to work by
student), with a subform bound to tblStudentSubject. The subform is in
continuous form view, and has a combo box for choosing the subject.

 

[John W. Vinson]

the purpose of this DB is to allow a teacher to locate a student and then
select the recommended course for that student in each of the subject areas:
Math, English, Science, Soc Studies, and Electives. For exampl, a teacher
selects student Joe Smith from the name cbo and English IV Honors from the
english cbo, which should then write the student id and english id choices to
a new record in the tblStudentEnglish. tblStudentEnglish will contain a
record of each student and the section he/she is recommended for.

i believe this is identical to your example w/ Client and Diseases. I just
cant get my frmStudent to write to tblStudentEnglish the way your frmClient
does for tblClientDiseases.

Allen has a tblClientDiseases. He doesn't have a tblHypertension or a
tblDiabetes or a tblCancer!

You may want to consider a form based on tblStudent, with a Combo Box to find
the record for a student by name (the wizard will build one); you may have
this. The Subform could be based on tblStudentClasses, with the Master/Child
Link Field being the StudentID to copy the selected student's ID into the
child table. On the subform you'ld have a combo box *to select which class* -
a record for English, a record for Math, etc. You certainly do not need a
separate table for each subject!

 

[mike]

how do i write the value for the selected student and the selected class on
the form to create a new record in the tblStudentSubject? is there any code
or does it do automatically when i create the form using the appropriate
relationships?

 

[John W. Vinson]

how do i write the value for the selected student

You don't "write it". You select the student's name from a Combo Box control
showing alphabetical names, but actually returning the unique StudentID. If
you use the combo box wizard, this will bring up all the information about
this student onto the form, for verification or (if necessary) editing. You
never even need to *SEE* the studentID, much less type it.

and the selected class

Again, you would simply have a combo box on the tblStudentSubject subform;
this would display the class name (whatever a human needs to recognize which
class is which), and store the ClassID into the table.

on
the form to create a new record in the tblStudentSubject? is there any code
or does it do automatically when i create the form using the appropriate
relationships?

Neither.

You don't need VBA code to do this - just the tools (forms, subforms, and
combo boxes) that Access provides.

But the relationships don't automagically add data to your table; all a
relationship does is to *prevent* you from adding a nonexistant student, or a
nonexistant class, perserving the integrity of your data.

 

[mike]

i get what you guys are saying. however, my interface is not writing to
tblStudentSubject when i select a student and subject.

-use wizard to create form
-select tblStudent and add name field
-then delete name field in design view and replace it w/ a cbo displaying
student names in the hdr
-add subform to details
-select tblStudentSubject and add field(s)
-then delete fields and replace it w/ a cbo displaying subjects

*why cant i add a cbo in the wizard?
*at what step in the wizard am i specifying the selected name/subject will
add a new record to tblStudentSubject? (or is this a property setting?)

 

[mike]

is this right?

-the cbo in the main form is interface for tblStudent
-the sub form is interface for tblStudentSubject
-the cbo in sub form is interface for tblSubject

 

[John W. Vinson]

is this right?

-the cbo in the main form is interface for tblStudent
-the sub form is interface for tblStudentSubject
-the cbo in sub form is interface for tblSubject

Well... sort of depends on what you mean by "interface".

The mainform would be based on tblStudent, and allow you to enter new
students, or edit or display information about a student.

A Combo box gets data from one place (typically a query), its Row Source, and
gives you the opportunity to select one record and one field (the Bound
Column) from that record. This tool is commonly used in two different manners:
unbound, or bound.

You can have an "Unbound" combo box on the mainform. It would (like the
mainform) be based on tblStudent as its Row Source. It would be unbound
because for this combo, you don't want to *change* anyone's student ID; you
just want to select a student ID and display that student's information on the
form, using some VBA code in the combo's AfterUpdate event. The toolbox Combo
Box wizard will build this for you.

The Subform displays data from tblStudentSubject. Its Master Link Field would
be the StudentID field from the mainform, and its Child Link Field would be
the StudentID field in tblStudentSubject. This keeps the subform synchronized
with the currently selected record on the mainform; if the mainform is showing
data on Anita Rodriguez, the subform will show the classes in which she is
enrolled, and let you add new records which will automatically inherit her
student ID.

You can also have a Bound combo box - e.g. the Subject combo box on the
subform; this combo will display the subject name (and any other info that you
need to make a selection), and store the SubjectID in its Control Source field
- the SubjectID in tblSubjectStudent.

This form-subform combination will not by itself let you add a new Subject -
you'ld have a separate form for that purpose.

Take a look at the Orders form in the Northwind sample database, it shows this
process in detail; the Order correstponds to a Student, the OrderDetails
subform to StudentSubject, and Products corresponds to Subject.

 

[mike]

thx so much John and Allen

Allen has a tblClientDiseases. He doesn't have a tblHypertension or a
tblDiabetes or a tblCancer!

You may want to consider a form based on tblStudent, with a Combo Box to find
the record for a student by name (the wizard will build one); you may have
this. The Subform could be based on tblStudentClasses, with the Master/Child
Link Field being the StudentID to copy the selected student's ID into the
child table. On the subform you'ld have a combo box *to select which class* -
a record for English, a record for Math, etc. You certainly do not need a
separate table for each subject!