Trying to create Teacher Day Book table structure

F

Fred Boer

Hello!

I am starting a new little project, and I am interested in advice or
suggestions about the structure. I want to create a database to help with my
classroom management. I want to be able to store data on the following:
Classes, students, class lists of students, class attendance by students,
daily class lesson plans or "day sheets". Here's what I've got so far:

tblClasses
ClassID;ClassName;ClassStartDate

tblStudents
StudentID;StudentLastName;StudentMidName;StudentFirstName, etc...

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate

tblClassDaySheet
ClassDaysheetID;
ClassID;ClassDate;ClassLesson;ClassReading;ClassAssignment;Classnotes

I want to be able to create a form and/or a report that would allow me to
mark attendance for each class for each period taught, and to store
information about the lesson taught in each class for each period. Picture
me sitting down at my desk at the head of the class, marking attendance and
making notes about the class... Does this table structure seem reasonable?

Thanks for any suggestions!
Fred Boer
 
V

Vincent Johns

Fred said:
Hello!

I am starting a new little project, and I am interested in advice or
suggestions about the structure. I want to create a database to help with my
classroom management. I want to be able to store data on the following:
Classes, students, class lists of students, class attendance by students,
daily class lesson plans or "day sheets". Here's what I've got so far:

tblClasses
ClassID;ClassName;ClassStartDate

tblStudents
StudentID;StudentLastName;StudentMidName;StudentFirstName, etc...

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate

tblClassDaySheet
ClassDaysheetID;
ClassID;ClassDate;ClassLesson;ClassReading;ClassAssignment;Classnotes

I want to be able to create a form and/or a report that would allow me to
mark attendance for each class for each period taught, and to store
information about the lesson taught in each class for each period. Picture
me sitting down at my desk at the head of the class, marking attendance and
making notes about the class... Does this table structure seem reasonable?

Thanks for any suggestions!
Fred Boer
Click to expand...

Although I'm not sure what you intend to store in some of the fields, it
looks good. I suggest you try to normalize the database so that you
don't keep the same information in more than one place any more than
necessary. (You have to do some of that, as when you link records via
matching keys, but try to minimize it, to reduce the risk of having
inconsistent data. Sadly, it doesn't minimize the risk of wrong data,
but that's another story.)

For each task that you must do (e.g., recording attendance), you can set
up a form that will expose only the information necessary, such as in
this case showing in read-only fields the student's first name and last
initial, and a read-write field for recording attendance. (You might be
able to display the entire class on one form, so that you wouldn't have
to move around among records.) For updating contact information, you'd
need a more elaborate form.

Now that you've decided on the tables, I suggest that you design queries
matching the tasks you need to do. When you've debugged a query, use
the Forms wizard to convert it to a data-input form, or a Reports wizard
to design printable reports.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
L

Lynn Trapp

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate
Click to expand...


Fred,
I'm going to make an assumption here that the combination of StudentID and
ClassID in tblStudentClasses is unique. If that is the case, as it should
be, then why not simply use the StudentClassesID to identify that
combination in tblAttendance? You would, thus, have:

tblAttendance
AttendanceID
StudentClassesID
ClassDate

--
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/conradsystems/accessjunkie.html
 
F

Fred Boer

Hi Lynn:

Thanks! You are correct, of course. I hope to have time at lunch today to
play with this and make the change you suggest. I appreciate your help!

Cheers!
Fred
 
F

Fred Boer

Dear Lynn:

I'm floundering. I have managed to create a form, using the wizard, but I
don't really understand what I've done, or if I've done it correctly. I've
tried to outline the form I've created, and if you have the strength... ;)

Tables:

tblAttendance: AttendanceID; StudentClassID; AttendanceCode; ClassDate
tblClasses: ClassID; ClassName
tblDayClass: DayClassID; ClassID; DayClassDate; DayClassLesson;
DayClassNotes
tblStudentClass: StudentClassID; StudentID; ClassID
tblStudents: StudentID; StudentLastName, StudentFirstname, etc.

The tables for classes and students are straightforward.
The table "tblStudentClass" gives me a record for each student in each
class.
The table "tblDayClass" gives me a daily record for each class taught.
The table "tblAttendance" gives me a daily record for each student in each
class.

I want a form (presumably using subforms) which will allow me to do daily
attendance and enter daily lesson information. With the help of the wizards,
I have created a form with two subforms.

Main form: Record source: tblClasses
SubformA: Record source: tblDayClasses (master/child link: ClassID)
SubformB: RecordSource: tblClasses (master/child link: ClassID)
SubformB has two subforms of its own:
SubformBA: RecordSource:
SELECT tblStudents.StudentLastName, tblStudentClasses.StudentClassID,
tblStudentClasses.ClassID
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID; (master/child link: ClassID)

SubformBB: RecordSource: tblAttendance (master/child link:
StudentClassID/SubformBB.Form![StudentClassID]

Can you make any sense of all that? Apologies if I haven't described it
well. I have a sense that SubformB, since it has the same recordsource and
master/child link as subformA might not be necessary?

Thanks!
Fred

P.S. Don't you like the word "floundering"? It's such a visually rich
word... and so precisely descriptive of my state.. ;)
 
F

Fred Boer

Just to add an additional comment: After further exploration the form I've
created doesn't work as I thought or envisioned. I have all the information
displayed but not linked as I'd hoped. Changing the record selected in
SubformA (the daily lesson plan record), doesn't change the attendance
record to match... Floundering indeed... :)

Thanks
Fred
 
F

Fred Boer

Hi Lynn:

Great idea. I am heading home myself. If I am lucky I will have time to try
that tonight or tomorrow. I'll let you know how it goes!

Fred

Lynn Trapp said:
Fred,
Just a quick comment before I head home for the day. You are right that
you probably don't need subformB. Have you tried putting the multiple
subforms on a Tab Control?

--
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/conradsystems/accessjunkie.html



Fred Boer said:
Dear Lynn:

I'm floundering. I have managed to create a form, using the wizard, but I
don't really understand what I've done, or if I've done it correctly.
I've tried to outline the form I've created, and if you have the
strength... ;)

Tables:

tblAttendance: AttendanceID; StudentClassID; AttendanceCode; ClassDate
tblClasses: ClassID; ClassName
tblDayClass: DayClassID; ClassID; DayClassDate; DayClassLesson;
DayClassNotes
tblStudentClass: StudentClassID; StudentID; ClassID
tblStudents: StudentID; StudentLastName, StudentFirstname, etc.

The tables for classes and students are straightforward.
The table "tblStudentClass" gives me a record for each student in each
class.
The table "tblDayClass" gives me a daily record for each class taught.
The table "tblAttendance" gives me a daily record for each student in
each class.

I want a form (presumably using subforms) which will allow me to do daily
attendance and enter daily lesson information. With the help of the
wizards, I have created a form with two subforms.

Main form: Record source: tblClasses
SubformA: Record source: tblDayClasses (master/child link: ClassID)
SubformB: RecordSource: tblClasses (master/child link: ClassID)
SubformB has two subforms of its own:
SubformBA: RecordSource:
SELECT tblStudents.StudentLastName, tblStudentClasses.StudentClassID,
tblStudentClasses.ClassID
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID; (master/child link: ClassID)

SubformBB: RecordSource: tblAttendance (master/child link:
StudentClassID/SubformBB.Form![StudentClassID]

Can you make any sense of all that? Apologies if I haven't described it
well. I have a sense that SubformB, since it has the same recordsource
and master/child link as subformA might not be necessary?

Thanks!
Fred

P.S. Don't you like the word "floundering"? It's such a visually rich
word... and so precisely descriptive of my state.. ;)
Click to expand...
Click to expand...
 
F

Fred Boer

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
______________________________________________________________
 
F

Fred Boer

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.
Class>LessonNotesSubform>AttendanceSubsubform? Because Attendance is linked
to the lesson information, not the class information?

Not sure if that will help!

Cheers!
Fred
 
V

Vincent Johns

Fred said:
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.
Class>LessonNotesSubform>AttendanceSubsubform? Because Attendance is linked
to the lesson information, not the class information?

Not sure if that will help!

Cheers!
Fred
Click to expand...

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 <[email protected]>
Please feel free to quote anything I say here.
 
F

Fred Boer

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
 
F

Fred Boer

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
 
J

John Vinson

in message:


LOL!

Four words Fred:
"Field Day Report Database"

;-)
Click to expand...

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]
 
J

Jeff Conrad

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

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

<vbg>
 
Top