Trying to create Teacher Day Book table structure

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;StudentFi rstName, etc...

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate

tblClassDaySheet
ClassDaysheetID;
ClassID;ClassDate;ClassLesson;ClassReading;ClassAs signment;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

Fred Boer wrote:

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;StudentFi rstName, etc...

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate

tblClassDaySheet
ClassDaysheetID;
ClassID;ClassDate;ClassLesson;ClassReading;ClassAs signment;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

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
Please feel free to quote anything I say here.

Dear Vincent:

Thanks for the help! I'll be tackling the queries next...

Fred

"Vincent Johns" wrote in message
news
Fred Boer wrote:

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;StudentFi rstName, etc...

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate

tblClassDaySheet
ClassDaysheetID;
ClassID;ClassDate;ClassLesson;ClassReading;ClassAs signment;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

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
Please feel free to quote anything I say here.

tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate


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/conrad...essjunkie.html

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

"Lynn Trapp" wrote in message
...
tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate


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/conrad...essjunkie.html

You're quite welcome fred. Good luck with it.

--
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
...
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

"Lynn Trapp" wrote in message
...
tblStudentClasses
StudentClassesID;StudentID;ClassID

tblAttendance
AttendanceID;StudentID,ClassID,ClassDate


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/conrad...essjunkie.html

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..

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

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" wrote in message
...
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/conrad...essjunkie.html



"Fred Boer" wrote in message
...
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..

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/conrad...essjunkie.html



"Fred Boer" wrote in message
...
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..