multi-record for

I am trying to create an attendence database. I have created my tables. I
would like to have a form that pulls in each student in a class. The student
attendance table (by date) would be updated by selecting present/absent,
excused/not excused, tardy/not tardy, and school/home lunch. I do not know
how to connect the updates with the student. Any suggestions would be
appreciated. Thanks
--
tdwright

Hi, td.

It always helps me to get started by thinking about things or people (which
become tables) and relationships.

In your case, you have a Students table, and ClassEvents. These tables have
a many-to-many relationship--many students attend each class event, and each
student attends many class events. This means you need an intermediate table
in a one-to-many relationship with each to represent it. This suggests the
following structures:

Students
----------------
StudentID AutoNumber or Integer (Primary Key)
FName Text
LName Text
Phone Text
....other student-specific attributes

ClassEvents
--------------
ClassEventID AutoNumber (Primary Key)
ClassDate Date/Time
....any other class date-specific fields


StudentClassEvents (Intermediate Table)
------------------------
StudentClassEventID AutoNumber (Primary Key)
StudentID Integer (Foreign Key to Students)
ClassEventID Integer (Foreign Key to ClassEvents)
AttendanceStatus Integer (Foreign Key to AttendanceStatus) or Text
LunchStatus Integer (Foreign Key to LunchStatus) or Text

There are to be (number of class days) * (number of students) records in
StudentClassEvents. Since you will likely enter the attendance of all
students on a particular day, rather than all the days for a particular
student, build a main form on ClassEvents with a continuous subform, linked
on the ClassEventID. Since the name fields are not in the StudentClassEvents
table, base the subform on a query of the StudentClassEvents and Students
tables, linked by the StudentID. Select all of the fields of the
intermediate table, and a calculated field for the name: FullName:[LName] &
", " & [FName], sorted in Ascending Order.

The subform need not have all of the fields in the query, however, just the
FullName and the status fields.

This type of application should be "preloaded", that is, first load all of
the class days into ClassEvents, and then run an insert query to insert
records into StudentClassEvents, one for each student for each day. Then you
can use your form to update the status fields each day.

Hope that helps.

Sprinks

"tdwright" wrote:

I am trying to create an attendence database. I have created my tables. I
would like to have a form that pulls in each student in a class. The student
attendance table (by date) would be updated by selecting present/absent,
excused/not excused, tardy/not tardy, and school/home lunch. I do not know
how to connect the updates with the student. Any suggestions would be
appreciated. Thanks
--
tdwright

This is for grade school/high school kids, so attendance is taken only once
per day. Would this make any difference in the tables?
--
tdwright


"Sprinks" wrote:

Hi, td.

It always helps me to get started by thinking about things or people (which
become tables) and relationships.

In your case, you have a Students table, and ClassEvents. These tables have
a many-to-many relationship--many students attend each class event, and each
student attends many class events. This means you need an intermediate table
in a one-to-many relationship with each to represent it. This suggests the
following structures:

Students
----------------
StudentID AutoNumber or Integer (Primary Key)
FName Text
LName Text
Phone Text
...other student-specific attributes

ClassEvents
--------------
ClassEventID AutoNumber (Primary Key)
ClassDate Date/Time
...any other class date-specific fields


StudentClassEvents (Intermediate Table)
------------------------
StudentClassEventID AutoNumber (Primary Key)
StudentID Integer (Foreign Key to Students)
ClassEventID Integer (Foreign Key to ClassEvents)
AttendanceStatus Integer (Foreign Key to AttendanceStatus) or Text
LunchStatus Integer (Foreign Key to LunchStatus) or Text

There are to be (number of class days) * (number of students) records in
StudentClassEvents. Since you will likely enter the attendance of all
students on a particular day, rather than all the days for a particular
student, build a main form on ClassEvents with a continuous subform, linked
on the ClassEventID. Since the name fields are not in the StudentClassEvents
table, base the subform on a query of the StudentClassEvents and Students
tables, linked by the StudentID. Select all of the fields of the
intermediate table, and a calculated field for the name: FullName:[LName] &
", " & [FName], sorted in Ascending Order.

The subform need not have all of the fields in the query, however, just the
FullName and the status fields.

This type of application should be "preloaded", that is, first load all of
the class days into ClassEvents, and then run an insert query to insert
records into StudentClassEvents, one for each student for each day. Then you
can use your form to update the status fields each day.

Hope that helps.

Sprinks

"tdwright" wrote:

I am trying to create an attendence database. I have created my tables. I
would like to have a form that pulls in each student in a class. The student
attendance table (by date) would be updated by selecting present/absent,
excused/not excused, tardy/not tardy, and school/home lunch. I do not know
how to connect the updates with the student. Any suggestions would be
appreciated. Thanks
--
tdwright

Hi, td.

Not the structures, but you might call the second two tables Days and
StudentDays instead of ClassEvents and StudentClassEvents. The basic needs
are the same--you need a record for each student on each day in the
intermediate table.

Sprinks

"tdwright" wrote:

This is for grade school/high school kids, so attendance is taken only once
per day. Would this make any difference in the tables?
--
tdwright


"Sprinks" wrote:

Hi, td.

It always helps me to get started by thinking about things or people (which
become tables) and relationships.

In your case, you have a Students table, and ClassEvents. These tables have
a many-to-many relationship--many students attend each class event, and each
student attends many class events. This means you need an intermediate table
in a one-to-many relationship with each to represent it. This suggests the
following structures:

Students
----------------
StudentID AutoNumber or Integer (Primary Key)
FName Text
LName Text
Phone Text
...other student-specific attributes

ClassEvents
--------------
ClassEventID AutoNumber (Primary Key)
ClassDate Date/Time
...any other class date-specific fields


StudentClassEvents (Intermediate Table)
------------------------
StudentClassEventID AutoNumber (Primary Key)
StudentID Integer (Foreign Key to Students)
ClassEventID Integer (Foreign Key to ClassEvents)
AttendanceStatus Integer (Foreign Key to AttendanceStatus) or Text
LunchStatus Integer (Foreign Key to LunchStatus) or Text

There are to be (number of class days) * (number of students) records in
StudentClassEvents. Since you will likely enter the attendance of all
students on a particular day, rather than all the days for a particular
student, build a main form on ClassEvents with a continuous subform, linked
on the ClassEventID. Since the name fields are not in the StudentClassEvents
table, base the subform on a query of the StudentClassEvents and Students
tables, linked by the StudentID. Select all of the fields of the
intermediate table, and a calculated field for the name: FullName:[LName] &
", " & [FName], sorted in Ascending Order.

The subform need not have all of the fields in the query, however, just the
FullName and the status fields.

This type of application should be "preloaded", that is, first load all of
the class days into ClassEvents, and then run an insert query to insert
records into StudentClassEvents, one for each student for each day. Then you
can use your form to update the status fields each day.

Hope that helps.

Sprinks

"tdwright" wrote:

I am trying to create an attendence database. I have created my tables. I
would like to have a form that pulls in each student in a class. The student
attendance table (by date) would be updated by selecting present/absent,
excused/not excused, tardy/not tardy, and school/home lunch. I do not know
how to connect the updates with the student. Any suggestions would be
appreciated. Thanks
--
tdwright