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!
4  2000 
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!
"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."
--
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!
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!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Howard Nease |
last post by:
Hello, everyone. I would appreciate any advice that someone could give me on
my future career path. Here is my situation:
I am a bright Junior in a very well-respected private high school, taking...
|
by: Richard Jones |
last post by:
Garbage Collection & Memory Management
Summer School
20-21 July 2004, Canterbury, UK
The performance of today's memory-hungry applications depends on
efficient dynamic memory management,...
|
by: Rare Book School |
last post by:
RARE BOOK SCHOOL 2005
Rare Book School is pleased to announce its schedule of courses for
2005, including sessions at the University of Virginia, the Walters Art
Museum/Johns Hopkins University...
|
by: Volkan Arslan |
last post by:
-------------------------------------------------------------
LASER Summer School on Software Engineering
Practical Techniques of Software Quality
Elba, Italy
September 12 - 18, 2004
...
|
by: shan |
last post by:
I have tried writing a program which should
1)get a number from user
2)calculate the following
for e.g if the input is 3 then 3
3
3
and the answer should be 19683.
3)similarly when the...
|
by: Volkan Arslan |
last post by:
-------------------------------------------------------------
LASER Summer School on Software Engineering
Software engineering for concurrent and real-time systems
Elba, Italy
September 11 -...
|
by: shahram.shirazi |
last post by:
Hi guys,
I was wondering if someone could help me a bit here. Im trying to
desing an electronic register system for a school. In terms of the
table design, I obviously need a Student Details...
|
by: Lockwood |
last post by:
someone check out this program and tell me what im doing wrong please...every time i fix some errors i get even more than before (right now theres 10). note that im a beginner/noob, and that this...
|
by: JacD |
last post by:
I have an access database (MS Access 2002,) which stores school pupils information and I have very limited knowledge on VBA. Hopefully someone can help solve this one for me.
I have a form...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| | |
