Trying to create Teacher Day Book table structure

Hello!

Well, Lynn, I'm still struggling without success. This seems really hard to
me, which, as I've learned from bitter experience, usually means I'm doing
something fundamentally wrong. Could I be structuring the tables wrong?
Would you (or anyone else who cares to!) be willing to walk through this
with me?

Restating the situation (for those late to this party...)

I want to create a database to track lessons taught in my classes. This
database should also be able to track student registration and attendance in
classes. I want to create a form/report which will show me a list of
students in the class, allow me to mark attendance and provide me with
fields to display the lesson, readings, notes, etc.

Tables:

tblClasses: ClassID,Classname, etc.
tblStudents: StudentID, StudentLastName, etc.
tblStudentClasses: StudentClassesID,StudentID,ClassID
tblDayClasses: DayClassesID, ClassID, ClassLessonDate, ClassLessonTopic;
ClassLessonReadings, ClassLessonNotes, etc. (There would a new record here
for every lesson taught - a daily record of the lesson of the day)
tblAttendance: AttendanceID; studentclassesID; AttendanceCode;
AttendanceDate

I can't seem to find a way to link these tables together to generate the
form/report I want.

I know we shouldn't design from reports, but, if it helps here is how I
envision the report/form. There would be a form/report so that the
information below would be available for every lesson taught in each class:

_____________

Monday, Septermber 12, 2005


Class Name: BTA301-01

Students: Attendance:

Boer, Fred Present
Conrad, Jeff Absent
Goldgar, Dirk Present
Trapp, Lynn Present

LESSON:

The development of ice cream through the ages.

READING:

Textbooks: Pages 12-15

ASSIGNMENT:

Write an essay on the benefits of frozen treats in a balanced diet.

NOTES:

Detention for Jeff for rowdy behaviour
__________________________________________________ ____________

Fred,
The table structure looks fine to me. I don't think I understand exactly
where you are experiencing the problem. Did you use a tab control on your
main form?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Fred Boer" wrote in message
...
Hello!

Well, Lynn, I'm still struggling without success. This seems really hard
to me, which, as I've learned from bitter experience, usually means I'm
doing something fundamentally wrong. Could I be structuring the tables
wrong? Would you (or anyone else who cares to!) be willing to walk through
this with me?

Restating the situation (for those late to this party...)

I want to create a database to track lessons taught in my classes. This
database should also be able to track student registration and attendance
in classes. I want to create a form/report which will show me a list of
students in the class, allow me to mark attendance and provide me with
fields to display the lesson, readings, notes, etc.

Tables:

tblClasses: ClassID,Classname, etc.
tblStudents: StudentID, StudentLastName, etc.
tblStudentClasses: StudentClassesID,StudentID,ClassID
tblDayClasses: DayClassesID, ClassID, ClassLessonDate, ClassLessonTopic;
ClassLessonReadings, ClassLessonNotes, etc. (There would a new record here
for every lesson taught - a daily record of the lesson of the day)
tblAttendance: AttendanceID; studentclassesID; AttendanceCode;
AttendanceDate

I can't seem to find a way to link these tables together to generate the
form/report I want.

I know we shouldn't design from reports, but, if it helps here is how I
envision the report/form. There would be a form/report so that the
information below would be available for every lesson taught in each
class:

_____________

Monday, Septermber 12, 2005


Class Name: BTA301-01

Students: Attendance:

Boer, Fred Present
Conrad, Jeff Absent
Goldgar, Dirk Present
Trapp, Lynn Present

LESSON:

The development of ice cream through the ages.

READING:

Textbooks: Pages 12-15

ASSIGNMENT:

Write an essay on the benefits of frozen treats in a balanced diet.

NOTES:

Detention for Jeff for rowdy behaviour
__________________________________________________ ____________

Hi Lynn:

Thanks for helping! I can create a nice form with a tab control. The first
tab control page contains a subform which displays the lesson notes. No
problem. The second tab control page contains a subform which displays the
students registered in each class. Again, no problem. The problem comes with
tracking the attendance. I want a list of students for each lesson
presented, with a field to mark their attendance. This is where I am
stalled. I can't seem to pull together the class name, the specific class
daily lesson, the list of students for that specific class daily lesson, and
the attendance field for each student.

Is it possible that I need a sub-subform, i.e.
ClassLessonNotesSubformAttendanceSubsubform? Because Attendance is linked
to the lesson information, not the class information?

Not sure if that will help!

Cheers!
Fred



"Lynn Trapp" wrote in message
...
Fred,
The table structure looks fine to me. I don't think I understand exactly
where you are experiencing the problem. Did you use a tab control on your
main form?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Fred Boer" wrote in message
...
Hello!

Well, Lynn, I'm still struggling without success. This seems really hard
to me, which, as I've learned from bitter experience, usually means I'm
doing something fundamentally wrong. Could I be structuring the tables
wrong? Would you (or anyone else who cares to!) be willing to walk
through this with me?

Restating the situation (for those late to this party...)

I want to create a database to track lessons taught in my classes. This
database should also be able to track student registration and attendance
in classes. I want to create a form/report which will show me a list of
students in the class, allow me to mark attendance and provide me with
fields to display the lesson, readings, notes, etc.

Tables:

tblClasses: ClassID,Classname, etc.
tblStudents: StudentID, StudentLastName, etc.
tblStudentClasses: StudentClassesID,StudentID,ClassID
tblDayClasses: DayClassesID, ClassID, ClassLessonDate, ClassLessonTopic;
ClassLessonReadings, ClassLessonNotes, etc. (There would a new record
here for every lesson taught - a daily record of the lesson of the day)
tblAttendance: AttendanceID; studentclassesID; AttendanceCode;
AttendanceDate

I can't seem to find a way to link these tables together to generate the
form/report I want.

I know we shouldn't design from reports, but, if it helps here is how I
envision the report/form. There would be a form/report so that the
information below would be available for every lesson taught in each
class:

_____________

Monday, Septermber 12, 2005


Class Name: BTA301-01

Students: Attendance:

Boer, Fred Present
Conrad, Jeff Absent
Goldgar, Dirk Present
Trapp, Lynn Present

LESSON:

The development of ice cream through the ages.

READING:

Textbooks: Pages 12-15

ASSIGNMENT:

Write an essay on the benefits of frozen treats in a balanced diet.

NOTES:

Detention for Jeff for rowdy behaviour
__________________________________________________ ____________

Fred Boer wrote:

Hi Lynn:

Thanks for helping! I can create a nice form with a tab control. The first
tab control page contains a subform which displays the lesson notes. No
problem. The second tab control page contains a subform which displays the
students registered in each class. Again, no problem. The problem comes with
tracking the attendance. I want a list of students for each lesson
presented, with a field to mark their attendance. This is where I am
stalled. I can't seem to pull together the class name, the specific class
daily lesson, the list of students for that specific class daily lesson, and
the attendance field for each student.

Is it possible that I need a sub-subform, i.e.
ClassLessonNotesSubformAttendanceSubsubform? Because Attendance is linked
to the lesson information, not the class information?

Not sure if that will help!

Cheers!
Fred

I can see why you might want a sub-sub-subform, if on bringing up the
page you select the class, then the date, then the lesson, etc.

But you might do just as well (maybe it would be easier for the user) to
set up controls on your Form to let the user select those properties,
then filter the attendance records accordingly.

Does the lesson name (e.g., "The development of ice cream through the
ages") characterize the session? Or would it be easier to identify it
by time of day (which would fit in nicely with the date field)? If
date, time, and class name would be enough to identify the class
meeting, you might filter the students' names on those values and
display (in the innermost subform) the name field and attendance-status
field.

-- Vincent Johns
Please feel free to quote anything I say here.


"Lynn Trapp" wrote in message
...

Fred,
The table structure looks fine to me. I don't think I understand exactly
where you are experiencing the problem. Did you use a tab control on your
main form?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Fred Boer" wrote in message
...

Hello!

Well, Lynn, I'm still struggling without success. This seems really hard
to me, which, as I've learned from bitter experience, usually means I'm
doing something fundamentally wrong. Could I be structuring the tables
wrong? Would you (or anyone else who cares to!) be willing to walk
through this with me?

Restating the situation (for those late to this party...)

I want to create a database to track lessons taught in my classes. This
database should also be able to track student registration and attendance
in classes. I want to create a form/report which will show me a list of
students in the class, allow me to mark attendance and provide me with
fields to display the lesson, readings, notes, etc.

Tables:

tblClasses: ClassID,Classname, etc.
tblStudents: StudentID, StudentLastName, etc.
tblStudentClasses: StudentClassesID,StudentID,ClassID
tblDayClasses: DayClassesID, ClassID, ClassLessonDate, ClassLessonTopic;
ClassLessonReadings, ClassLessonNotes, etc. (There would a new record
here for every lesson taught - a daily record of the lesson of the day)
tblAttendance: AttendanceID; studentclassesID; AttendanceCode;
AttendanceDate

I can't seem to find a way to link these tables together to generate the
form/report I want.

I know we shouldn't design from reports, but, if it helps here is how I
envision the report/form. There would be a form/report so that the
information below would be available for every lesson taught in each
class:

_____________

Monday, Septermber 12, 2005


Class Name: BTA301-01

Students: Attendance:

Boer, Fred Present
Conrad, Jeff Absent
Goldgar, Dirk Present
Trapp, Lynn Present

LESSON:

The development of ice cream through the ages.

READING:

Textbooks: Pages 12-15

ASSIGNMENT:

Write an essay on the benefits of frozen treats in a balanced diet.

NOTES:

Detention for Jeff for rowdy behaviour
_______________________________________________ _______________

Dear Lynn:

Well, I've been making *some* progress. I now have a working form. Main form
based on ClassName with one subform listing student names, and a second
subform listing attendance information. I *think* it is working correctly,
that is, it is storing attendance information correctly. So that's good!

However, to this point, it doesn't work at all the way I want it to,
so....that's bad! Entering attendance is far too cumbersome. Also, I
imagined some process by which the creation of a new record for a class
lesson would automagically generate a new attendance record for each student
for that lesson, leaving me to simply enter a single attendance code.
Anyway, I have something to hammer away at!

Thanks!
Fred



"Lynn Trapp" wrote in message
...
Fred,
The table structure looks fine to me. I don't think I understand exactly
where you are experiencing the problem. Did you use a tab control on your
main form?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Fred Boer" wrote in message
...
Hello!

Well, Lynn, I'm still struggling without success. This seems really hard
to me, which, as I've learned from bitter experience, usually means I'm
doing something fundamentally wrong. Could I be structuring the tables
wrong? Would you (or anyone else who cares to!) be willing to walk
through this with me?

Restating the situation (for those late to this party...)

I want to create a database to track lessons taught in my classes. This
database should also be able to track student registration and attendance
in classes. I want to create a form/report which will show me a list of
students in the class, allow me to mark attendance and provide me with
fields to display the lesson, readings, notes, etc.

Tables:

tblClasses: ClassID,Classname, etc.
tblStudents: StudentID, StudentLastName, etc.
tblStudentClasses: StudentClassesID,StudentID,ClassID
tblDayClasses: DayClassesID, ClassID, ClassLessonDate, ClassLessonTopic;
ClassLessonReadings, ClassLessonNotes, etc. (There would a new record
here for every lesson taught - a daily record of the lesson of the day)
tblAttendance: AttendanceID; studentclassesID; AttendanceCode;
AttendanceDate

I can't seem to find a way to link these tables together to generate the
form/report I want.

I know we shouldn't design from reports, but, if it helps here is how I
envision the report/form. There would be a form/report so that the
information below would be available for every lesson taught in each
class:

_____________

Monday, Septermber 12, 2005


Class Name: BTA301-01

Students: Attendance:

Boer, Fred Present
Conrad, Jeff Absent
Goldgar, Dirk Present
Trapp, Lynn Present

LESSON:

The development of ice cream through the ages.

READING:

Textbooks: Pages 12-15

ASSIGNMENT:

Write an essay on the benefits of frozen treats in a balanced diet.

NOTES:

Detention for Jeff for rowdy behaviour
__________________________________________________ ____________

Dear Mr. Johns:

Just noticed your post after I sent my latest.. I haven't time at the moment
to read your post carefully, but I hope to do so later tonight and may
respond then.

Thanks!
Fred

"Vincent Johns" wrote in message
ink.net...
Fred Boer wrote:

Hi Lynn:

Thanks for helping! I can create a nice form with a tab control. The
first tab control page contains a subform which displays the lesson
notes. No problem. The second tab control page contains a subform which
displays the students registered in each class. Again, no problem. The
problem comes with tracking the attendance. I want a list of students for
each lesson presented, with a field to mark their attendance. This is
where I am stalled. I can't seem to pull together the class name, the
specific class daily lesson, the list of students for that specific class
daily lesson, and the attendance field for each student.

Is it possible that I need a sub-subform, i.e.
ClassLessonNotesSubformAttendanceSubsubform? Because Attendance is
linked to the lesson information, not the class information?

Not sure if that will help!

Cheers!
Fred

I can see why you might want a sub-sub-subform, if on bringing up the page
you select the class, then the date, then the lesson, etc.

But you might do just as well (maybe it would be easier for the user) to
set up controls on your Form to let the user select those properties, then
filter the attendance records accordingly.

Does the lesson name (e.g., "The development of ice cream through the
ages") characterize the session? Or would it be easier to identify it by
time of day (which would fit in nicely with the date field)? If date,
time, and class name would be enough to identify the class meeting, you
might filter the students' names on those values and display (in the
innermost subform) the name field and attendance-status field.

-- Vincent Johns
Please feel free to quote anything I say here.


"Lynn Trapp" wrote in message
...

Fred,
The table structure looks fine to me. I don't think I understand exactly
where you are experiencing the problem. Did you use a tab control on your
main form?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"Fred Boer" wrote in message
...

Hello!

Well, Lynn, I'm still struggling without success. This seems really hard
to me, which, as I've learned from bitter experience, usually means I'm
doing something fundamentally wrong. Could I be structuring the tables
wrong? Would you (or anyone else who cares to!) be willing to walk
through this with me?

Restating the situation (for those late to this party...)

I want to create a database to track lessons taught in my classes. This
database should also be able to track student registration and
attendance in classes. I want to create a form/report which will show me
a list of students in the class, allow me to mark attendance and provide
me with fields to display the lesson, readings, notes, etc.

Tables:

tblClasses: ClassID,Classname, etc.
tblStudents: StudentID, StudentLastName, etc.
tblStudentClasses: StudentClassesID,StudentID,ClassID
tblDayClasses: DayClassesID, ClassID, ClassLessonDate, ClassLessonTopic;
ClassLessonReadings, ClassLessonNotes, etc. (There would a new record
here for every lesson taught - a daily record of the lesson of the day)
tblAttendance: AttendanceID; studentclassesID; AttendanceCode;
AttendanceDate

I can't seem to find a way to link these tables together to generate the
form/report I want.

I know we shouldn't design from reports, but, if it helps here is how I
envision the report/form. There would be a form/report so that the
information below would be available for every lesson taught in each
class:

_____________

Monday, Septermber 12, 2005


Class Name: BTA301-01

Students: Attendance:

Boer, Fred Present
Conrad, Jeff Absent
Goldgar, Dirk Present
Trapp, Lynn Present

LESSON:

The development of ice cream through the ages.

READING:

Textbooks: Pages 12-15

ASSIGNMENT:

Write an essay on the benefits of frozen treats in a balanced diet.

NOTES:

Detention for Jeff for rowdy behaviour
______________________________________________ ________________

"Fred Boer" wrote in message:
...

Conrad, Jeff Absent

Detention for Jeff for rowdy behavior

Fred, how can I have had rowdy behavior if I was not in class??

g
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

"Fred Boer" wrote in message:
...

Don't talk back to ME, young man! Double detention!

LOL!

Four words Fred:
"Field Day Report Database"

;-)
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

On Tue, 13 Sep 2005 19:24:13 -0700, "Jeff Conrad"
wrote:

"Fred Boer" wrote in message:
...

Don't talk back to ME, young man! Double detention!

LOL!

Four words Fred:
"Field Day Report Database"

;-)

All right, you two, behave! Or you'll BOTH be writing "I will not use
Lookup Fields" 1000 times on the whiteboard...


John W. Vinson[Most Vengeful Principal]

"John Vinson" wrote in message:
...

All right, you two, behave! Or you'll BOTH be writing "I will not use
Lookup Fields" 1000 times on the whiteboard...

Sigh......yes, Mr. Principal.

vbg
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html