week number madness!

[Guest]

I have a custom form that I would like teachers in our program to be able to
use to query our database for student attendance record information. The
problem is that our program is a continuous enrolment, continuing education
program that can have a student taking more than one course from more than
one teacher, having started at different times.

I would like to have only one form for ease of use purposes, that would
allow a teacher or administrative staff member to choose:
1. What student name?
2. What course (or a choice for all courses)
3. Return the corresponding teacher name for that/those course(s)
4. And then a choice for week numbers (or a choice for all)

Thank you in advance for any help in this matter.

 

[Rob Oldfield]

The tricky bit here is going to be to get the structure of your tables
correct - its going to need to be a many to many relationship. At first
look you need a student table, a teacher table and a course table - each of
those would just contain information about those things. The join table,
called CourseTaken for example, would have StudentID, TeacherID, and
CourseID, as well as things specific to the course taken such as date
started and the result of the course.

If you don't have that kind of structure (or something very similar) then
you're going to be in lots of trouble. If you do then your requirement will
just be a basic query of that CourseTaken table with links to the other
tables purely for the purpose of looking up names etc.

(This is assuming that a single course starting in a particular week will be
'taught' - at least as far as your admin system is concerned - by the same
teacher. If that isn't the case then things will need to get complicated.)

 

[Guest]

Thanks for your reply Rob...

The tricky bit here is going to be to get the structure of your tables
correct - its going to need to be a many to many relationship. At first
look you need a student table, a teacher table and a course table - each of
those would just contain information about those things. The join table,
called CourseTaken for example, would have StudentID, TeacherID, and
CourseID, as well as things specific to the course taken such as date
started and the result of the course.

If you don't have that kind of structure (or something very similar) then
you're going to be in lots of trouble. If you do then your requirement will
just be a basic query of that CourseTaken table with links to the other
tables purely for the purpose of looking up names etc.

(This is assuming that a single course starting in a particular week will be
'taught' - at least as far as your admin system is concerned - by the same
teacher. If that isn't the case then things will need to get complicated.)

Sorry to say then, that things, in fact, ARE complicated. Not only might one
teacher teach more than one course, but more than one teacher might teach
that same course, so we wind up with course codes followed by section numbers
to differentiate beteween teachers. And, as it is a continuous enrolment
environment, a student may start one course one week and another course a few
weeks later, while other students in that same course are coming and going
differently!!! The many to many relationships in my table structure are mind
boggling, I know.

What I really would like to do is come up with a syntax for a multi-select
form control that would allow for teachers to choose more than one week to
include in the report of attendance records, or allow a selection not
included in the table to facilitate them choosing ALL weeks at once.

Is this possible in my mixed-up mess?

Thanks again for your continued interest in this matter.

 

[Rob Oldfield]

Sorry to say then, that things, in fact, ARE complicated. Not only might one
teacher teach more than one course, but more than one teacher might teach
that same course, so we wind up with course codes followed by section numbers
to differentiate beteween teachers. And, as it is a continuous enrolment
environment, a student may start one course one week and another course a few
weeks later, while other students in that same course are coming and going
differently!!! The many to many relationships in my table structure are mind
boggling, I know.

What I really would like to do is come up with a syntax for a multi-select
form control that would allow for teachers to choose more than one week to
include in the report of attendance records, or allow a selection not
included in the table to facilitate them choosing ALL weeks at once.

Is this possible in my mixed-up mess?

Yes - (I think - if I understand what you're after correctly).

Just concentrating on the selection of teachers....

If you want to be able to select multiple teachers then you could just use a
multi-select list box based on the teachers table.

You could then extend that by using a union query to add something like
"(All)" onto the list... something like...

SELECT Teachers.TeacherID as TeachID, Teachers.TeacherName as TName
FROM Teachers
union select 0 as TeachID,"(All)" as TName from Teachers;

..... though I've added that one more for reference. It doesn't really make
sense to have an (All) option on a multiselect list although you could
always test for that option being selected and deselect any other
selections.

The way that I think you probably need to go would be to have a 'Select All'
tick box combined with the standard multi-select for individual teachers.
You could then enable/disable the listbox depending on what is chosen in the
tick box.

 

[Guest]

Rob wrote:
Just concentrating on the selection of teachers....

If you want to be able to select multiple teachers then you could just use a
multi-select list box based on the teachers table.

You could then extend that by using a union query to add something like
"(All)" onto the list... something like...

SELECT Teachers.TeacherID as TeachID, Teachers.TeacherName as TName
FROM Teachers
union select 0 as TeachID,"(All)" as TName from Teachers;

Do I understand this to mean that if the user leaves the combobox empty
that All will be selected? Is that what the "select 0" means? I'm afraid I'm
just getting my SQL feet wet!

...... though I've added that one more for reference. It doesn't really make
sense to have an (All) option on a multiselect list although you could
always test for that option being selected and deselect any other
selections.

The way that I think you probably need to go would be to have a 'Select All'
tick box combined with the standard multi-select for individual teachers.
You could then enable/disable the listbox depending on what is chosen in the
tick box.

Of course! I couldn't see the forest for the trees...

Thanks.

 

[Rob Oldfield]

Rob wrote:
SELECT Teachers.TeacherID as TeachID, Teachers.TeacherName as TName
FROM Teachers
union select 0 as TeachID,"(All)" as TName from Teachers;

Do I understand this to mean that if the user leaves the combobox empty
that All will be selected? Is that what the "select 0" means? I'm afraid I'm
just getting my SQL feet wet!

No. All that that will do will be to add in an additional entry in addition
to the standard list of teachers. It would normally be used where the list
isn't going to be multi-select. Just try pasting that into the SQL window
of a query and hopefully you'll see what you get. The 0 is just the ID
number that will be returned if the user selects the (All) option - it just
needs to be guaranteed different from existing teacher IDs - so assuming
that you're just using a standard incrementing autonumber it could be
anything less than 1.

The way that I think you probably need to go would be to have a 'Select All'
tick box combined with the standard multi-select for individual teachers.
You could then enable/disable the listbox depending on what is chosen in the
tick box.

Of course! I couldn't see the forest for the trees...

Good.