By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

Easy for you, Difficult for me: Help a school

P: n/a
n
Hello! Here is a problem I hope you can point me to a solution. It

Problem: A teacher needs to know which lesson to teach.

A school has a curriculum with 26 lessons, A-Z.

For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.

The teacher needs to quickly identify which, if any, of the lessons
in
the curriculum have not yet been taught to any of the students.

The teacher selects one of the untaught lessons and marks on each of
the student "cards" that they have now been taught that lesson by
that
teacher.

Example i

3 students arrive: 1,2 &3

1 has studied A, C and D;
2 has studied lessons A though W;
3 has studied A, C and E.

In this situation, it is easy to see that the teacher could teach
either X, Y or Z. (You can imagine it would be a lot more difficult
if
there were 10 students.)

The teacher would teach X, and mark on the "cards" that 1,2 and 3 had
now also studied X

Example ii

The same students arrive as in the above example, as well as student
4:

4 has studied B through Z

The teacher can see that it is impossible to teach this class a
lesson from the curriculum that has not already been covered already.
The teacher must therefore establish which lesson has either:

been taught to the fewest students
or
been taught to the students, but not by that teacher

OK, here is my paper and pencil way of doing this:

each student has a piece of paper three columns: A-Z in the first
colum, the adjacent colum is a check box "taught", and the adjacent
column is "initials" indicating the teacher that taught it. (This
field could have more than one entry, if the same lesson was taught to
the same student by different teachers. The field could be something
like Comma Separated Values.)

When a class forms, the papers are put on the table, the teacher
looks
at them, and finds a lesson not yet taught.

What would be the easy way to do this using Microsoft?
I think Access or Excel would be suitable, but maybe there is a kind
of pre-
built, sort of "prettier" way of doing this.

Thank you for your interest in this. I would really like your help!

Mar 6 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Step 1: Create 5 tables
=================
a) Student table (one record for each student), with StudentID primary key

b) Lesson table (one record for each lesson), with LessonID primary key

c) Teacher table (one record for each teacher), with TeacherID primary key

d) Session table (one record each time a teacher gives a lesson):
SessionID Primary key
TeacherID foreign key to Teacher.TeacherID
LessonID foreign key to Lesson.LessonID
SessionDateTime Date/time when the lesson occurred.

e) SessionStudent table (one record for each student in a session)
SessionStudentID Primary key
SessionID foreign key to Session.SessionID
StudentID foreign key to Student.StudentID
Step 2: Create 2 queries
==================
a) Create another query using the Student and Lesson tables.
No join.
Drag StudentID and LessonID into the Field row.
Save the query as qryStudentLessonProduct.
It gives every possible combination of student and lesson.

b) Create a query using the Session and SessionStudent tables.
Depress the Total button on the Toolbar.
Drag StudentID and LessonID into the Field row.
Accept Group By in the Total row under these fields.
Drag SessionDateTime into the grid, and choose Max in the Total row.
Drag SessionStudentID into the grid, and choose Count in the Total row.
Save the query as qrySessionStudent.
It gives the count of each time each student did each lesson (if done), and
the most recent date.

Step 3: Create the final query:
=====================
a) Create a third query using that qryStudentLessonProduct and
qrySessionStudent as input "tables."

Drag qryStudentLessonProduct.StudentID onto qrySessionStudent.StudentID.

Double-click the line Access draws between the 2 tables, and choose the
option:
All records from qryStudentLessonProduct, and any matches from...
This gives an outer join.

Drag qryStudentLessonProduct.LessionID onto qrySessionStudent.LessonID, and
turn it into an outer join again.

Drag the fields from from qryStudentLessonProduct into the grid.
Drag CountOfSessionStudentID into the grid.

You can now see every combination of student and lesson, and the number of
times each student has done each lesson (blank if not done.)

From there you can filter or add criteria as needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"n" <to**************@yahoo.comwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
Hello! Here is a problem I hope you can point me to a solution. It

Problem: A teacher needs to know which lesson to teach.

A school has a curriculum with 26 lessons, A-Z.

For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.

The teacher needs to quickly identify which, if any, of the lessons
in
the curriculum have not yet been taught to any of the students.

The teacher selects one of the untaught lessons and marks on each of
the student "cards" that they have now been taught that lesson by
that
teacher.

Example i

3 students arrive: 1,2 &3

1 has studied A, C and D;
2 has studied lessons A though W;
3 has studied A, C and E.

In this situation, it is easy to see that the teacher could teach
either X, Y or Z. (You can imagine it would be a lot more difficult
if
there were 10 students.)

The teacher would teach X, and mark on the "cards" that 1,2 and 3 had
now also studied X

Example ii

The same students arrive as in the above example, as well as student
4:

4 has studied B through Z

The teacher can see that it is impossible to teach this class a
lesson from the curriculum that has not already been covered already.
The teacher must therefore establish which lesson has either:

been taught to the fewest students
or
been taught to the students, but not by that teacher

OK, here is my paper and pencil way of doing this:

each student has a piece of paper three columns: A-Z in the first
colum, the adjacent colum is a check box "taught", and the adjacent
column is "initials" indicating the teacher that taught it. (This
field could have more than one entry, if the same lesson was taught to
the same student by different teachers. The field could be something
like Comma Separated Values.)

When a class forms, the papers are put on the table, the teacher
looks
at them, and finds a lesson not yet taught.

What would be the easy way to do this using Microsoft?
I think Access or Excel would be suitable, but maybe there is a kind
of pre-
built, sort of "prettier" way of doing this.

Thank you for your interest in this. I would really like your help!
Mar 6 '07 #2

P: n/a
"n" <to**************@yahoo.comwrote in news:1173162702.279437.175810
@h3g2000cwc.googlegroups.com:
Hello! Here is a problem I hope you can point me to a solution. It

Problem: A teacher needs to know which lesson to teach.

A school has a curriculum with 26 lessons, A-Z.

For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.

The teacher needs to quickly identify which, if any, of the lessons
in
the curriculum have not yet been taught to any of the students.

The teacher selects one of the untaught lessons and marks on each of
the student "cards" that they have now been taught that lesson by
that
teacher.

Example i

3 students arrive: 1,2 &3

1 has studied A, C and D;
2 has studied lessons A though W;
3 has studied A, C and E.

In this situation, it is easy to see that the teacher could teach
either X, Y or Z. (You can imagine it would be a lot more difficult
if
there were 10 students.)

The teacher would teach X, and mark on the "cards" that 1,2 and 3 had
now also studied X

Example ii

The same students arrive as in the above example, as well as student
4:

4 has studied B through Z

The teacher can see that it is impossible to teach this class a
lesson from the curriculum that has not already been covered already.
The teacher must therefore establish which lesson has either:

been taught to the fewest students
or
been taught to the students, but not by that teacher

OK, here is my paper and pencil way of doing this:

each student has a piece of paper three columns: A-Z in the first
colum, the adjacent colum is a check box "taught", and the adjacent
column is "initials" indicating the teacher that taught it. (This
field could have more than one entry, if the same lesson was taught to
the same student by different teachers. The field could be something
like Comma Separated Values.)

When a class forms, the papers are put on the table, the teacher
looks
at them, and finds a lesson not yet taught.

What would be the easy way to do this using Microsoft?
I think Access or Excel would be suitable, but maybe there is a kind
of pre-
built, sort of "prettier" way of doing this.

Thank you for your interest in this. I would really like your help!
If an educational system were so sterile and insensitive as to operate in
the fashion you describe I strongly suggest that no lesson at all be
taught. The teacher and students would benefit by using the time assigned
for the class by praying. That's what I did when I read your post:

"Holy May, Mother of God, be with us now and at the hour of our death."

--
Mar 6 '07 #3

P: n/a
n
Thank you so much for your help, Allen! (And the prayers, Lyle.)

I have tried to follow your instructions, and though I haven't managed
to get it to work yet, it has been v enjoyable to try and i am
learning lots of things.

Here is where I think the problems begin:

I am using Access 2007.
I create the tables OK, and assign suitable, consistent datatypes.
Once the tables are completed, I go to Database
Tools>Relationships>Show Table>
I double click on the primary key in Session table and create a link
to the Lesson table LessonID.
Then I do the same again from Session table to Teacher table, and
Session table to SessionStudent table.
And one more from SessionStudent table to Student table.

That is not a problem.

I create the first query, qryStudentLessonProduct, without problem, by
Create>Query Design>Add table

The second query I can't do!

I try the above method, but I cannot see LessonID listed in the box,
so I can't drag anything down...

Also, I cannot see SessionDateTime.

Later, I can't proceed because of the problems here.

I hope somebody could help! I am nearly there, and still
enthusiastic. :)
On Mar 6, 2:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Step 1: Create 5 tables
=================
a) Student table (one record for each student), with StudentID primary key

b) Lesson table (one record for each lesson), with LessonID primary key

c) Teacher table (one record for each teacher), with TeacherID primary key

d) Session table (one record each time a teacher gives a lesson):
SessionID Primary key
TeacherID foreign key to Teacher.TeacherID
LessonID foreign key to Lesson.LessonID
SessionDateTime Date/time when the lesson occurred.

e) SessionStudent table (one record for each student in a session)
SessionStudentID Primary key
SessionID foreign key to Session.SessionID
StudentID foreign key to Student.StudentID

Step 2: Create 2 queries
==================
a) Create another query using the Student and Lesson tables.
No join.
Drag StudentID and LessonID into the Field row.
Save the query as qryStudentLessonProduct.
It gives every possible combination of student and lesson.

b) Create a query using the Session and SessionStudent tables.
Depress the Total button on the Toolbar.
Drag StudentID and LessonID into the Field row.
Accept Group By in the Total row under these fields.
Drag SessionDateTime into the grid, and choose Max in the Total row.
Drag SessionStudentID into the grid, and choose Count in the Total row.
Save the query as qrySessionStudent.
It gives the count of each time each student did each lesson (if done), and
the most recent date.

Step 3: Create the final query:
=====================
a) Create a third query using that qryStudentLessonProduct and
qrySessionStudent as input "tables."

Drag qryStudentLessonProduct.StudentID onto qrySessionStudent.StudentID.

Double-click the line Access draws between the 2 tables, and choose the
option:
All records from qryStudentLessonProduct, and any matches from...
This gives an outer join.

Drag qryStudentLessonProduct.LessionID onto qrySessionStudent.LessonID, and
turn it into an outer join again.

Drag the fields from from qryStudentLessonProduct into the grid.
Drag CountOfSessionStudentID into the grid.

You can now see every combination of student and lesson, and the number of
times each student has done each lesson (blank if not done.)

From there you can filter or add criteria as needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"n" <total_utter_c...@yahoo.comwrote in message

news:11**********************@h3g2000cwc.googlegro ups.com...
Hello! Here is a problem I hope you can point me to a solution. It
Problem: A teacher needs to know which lesson to teach.
A school has a curriculum with 26 lessons, A-Z.
For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.
The teacher needs to quickly identify which, if any, of the lessons
in
the curriculum have not yet been taught to any of the students.
The teacher selects one of the untaught lessons and marks on each of
the student "cards" that they have now been taught that lesson by
that
teacher.
Example i
3 students arrive: 1,2 &3
1 has studied A, C and D;
2 has studied lessons A though W;
3 has studied A, C and E.
In this situation, it is easy to see that the teacher could teach
either X, Y or Z. (You can imagine it would be a lot more difficult
if
there were 10 students.)
The teacher would teach X, and mark on the "cards" that 1,2 and 3 had
now also studied X
Example ii
The same students arrive as in the above example, as well as student
4:
4 has studied B through Z
The teacher can see that it is impossible to teach this class a
lesson from the curriculum that has not already been covered already.
The teacher must therefore establish which lesson has either:
been taught to the fewest students
or
been taught to the students, but not by that teacher
OK, here is my paper and pencil way of doing this:
each student has a piece of paper three columns: A-Z in the first
colum, the adjacent colum is a check box "taught", and the adjacent
column is "initials" indicating the teacher that taught it. (This
field could have more than one entry, if the same lesson was taught to
the same student by different teachers. The field could be something
like Comma Separated Values.)
When a class forms, the papers are put on the table, the teacher
looks
at them, and finds a lesson not yet taught.
What would be the easy way to do this using Microsoft?
I think Access or Excel would be suitable, but maybe there is a kind
of pre-
built, sort of "prettier" way of doing this.
Thank you for your interest in this. I would really like your help!

Mar 7 '07 #4

P: n/a
Step 2b is the query that uses Session and SessionStudent tables.
You can see LessonID in the Session table.
You can also see SessionDateTime in the Session table.
If you can't see these, add the Session table to the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"n" <to**************@yahoo.comwrote in message
news:11*********************@30g2000cwc.googlegrou ps.com...
Thank you so much for your help, Allen! (And the prayers, Lyle.)

I have tried to follow your instructions, and though I haven't managed
to get it to work yet, it has been v enjoyable to try and i am
learning lots of things.

Here is where I think the problems begin:

I am using Access 2007.
I create the tables OK, and assign suitable, consistent datatypes.
Once the tables are completed, I go to Database
Tools>Relationships>Show Table>
I double click on the primary key in Session table and create a link
to the Lesson table LessonID.
Then I do the same again from Session table to Teacher table, and
Session table to SessionStudent table.
And one more from SessionStudent table to Student table.

That is not a problem.

I create the first query, qryStudentLessonProduct, without problem, by
Create>Query Design>Add table

The second query I can't do!

I try the above method, but I cannot see LessonID listed in the box,
so I can't drag anything down...

Also, I cannot see SessionDateTime.

Later, I can't proceed because of the problems here.

I hope somebody could help! I am nearly there, and still
enthusiastic. :)
On Mar 6, 2:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>Step 1: Create 5 tables
=================
a) Student table (one record for each student), with StudentID primary
key

b) Lesson table (one record for each lesson), with LessonID primary key

c) Teacher table (one record for each teacher), with TeacherID primary
key

d) Session table (one record each time a teacher gives a lesson):
SessionID Primary key
TeacherID foreign key to Teacher.TeacherID
LessonID foreign key to Lesson.LessonID
SessionDateTime Date/time when the lesson occurred.

e) SessionStudent table (one record for each student in a session)
SessionStudentID Primary key
SessionID foreign key to Session.SessionID
StudentID foreign key to Student.StudentID

Step 2: Create 2 queries
==================
a) Create another query using the Student and Lesson tables.
No join.
Drag StudentID and LessonID into the Field row.
Save the query as qryStudentLessonProduct.
It gives every possible combination of student and lesson.

b) Create a query using the Session and SessionStudent tables.
Depress the Total button on the Toolbar.
Drag StudentID and LessonID into the Field row.
Accept Group By in the Total row under these fields.
Drag SessionDateTime into the grid, and choose Max in the Total row.
Drag SessionStudentID into the grid, and choose Count in the Total row.
Save the query as qrySessionStudent.
It gives the count of each time each student did each lesson (if done),
and
the most recent date.

Step 3: Create the final query:
=====================
a) Create a third query using that qryStudentLessonProduct and
qrySessionStudent as input "tables."

Drag qryStudentLessonProduct.StudentID onto qrySessionStudent.StudentID.

Double-click the line Access draws between the 2 tables, and choose the
option:
All records from qryStudentLessonProduct, and any matches from...
This gives an outer join.

Drag qryStudentLessonProduct.LessionID onto qrySessionStudent.LessonID,
and
turn it into an outer join again.

Drag the fields from from qryStudentLessonProduct into the grid.
Drag CountOfSessionStudentID into the grid.

You can now see every combination of student and lesson, and the number
of
times each student has done each lesson (blank if not done.)

From there you can filter or add criteria as needed.

"n" <total_utter_c...@yahoo.comwrote in message

news:11**********************@h3g2000cwc.googlegr oups.com...
Hello! Here is a problem I hope you can point me to a solution. It
Problem: A teacher needs to know which lesson to teach.
A school has a curriculum with 26 lessons, A-Z.
For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.
The teacher needs to quickly identify which, if any, of the lessons
in
the curriculum have not yet been taught to any of the students.
The teacher selects one of the untaught lessons and marks on each of
the student "cards" that they have now been taught that lesson by
that
teacher.
Example i
3 students arrive: 1,2 &3
1 has studied A, C and D;
2 has studied lessons A though W;
3 has studied A, C and E.
In this situation, it is easy to see that the teacher could teach
either X, Y or Z. (You can imagine it would be a lot more difficult
if
there were 10 students.)
The teacher would teach X, and mark on the "cards" that 1,2 and 3 had
now also studied X
Example ii
The same students arrive as in the above example, as well as student
4:
4 has studied B through Z
The teacher can see that it is impossible to teach this class a
lesson from the curriculum that has not already been covered already.
The teacher must therefore establish which lesson has either:
been taught to the fewest students
or
been taught to the students, but not by that teacher
OK, here is my paper and pencil way of doing this:
each student has a piece of paper three columns: A-Z in the first
colum, the adjacent colum is a check box "taught", and the adjacent
column is "initials" indicating the teacher that taught it. (This
field could have more than one entry, if the same lesson was taught to
the same student by different teachers. The field could be something
like Comma Separated Values.)
When a class forms, the papers are put on the table, the teacher
looks
at them, and finds a lesson not yet taught.
What would be the easy way to do this using Microsoft?
I think Access or Excel would be suitable, but maybe there is a kind
of pre-
built, sort of "prettier" way of doing this.
Thank you for your interest in this. I would really like your help!
Mar 7 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.