Creating an Attendance sheet in Access

[Guest]

I have a database I am creating for multiple classes. I have the tradition
Classes, Students, and Enrollements tables.

What I would like to do is create an Attendance sheet that would be filled
out every class period, thus electronically keeping the attendance of each
student.

I can't figure out how to accomplish this because I would need a new
attendance sheet for each day...

Any help would be greatly appreciated...thank you,

~ ivalum21

 

[John Vinson]

I have a database I am creating for multiple classes. I have the tradition
Classes, Students, and Enrollements tables.

What I would like to do is create an Attendance sheet that would be filled
out every class period, thus electronically keeping the attendance of each
student.

I can't figure out how to accomplish this because I would need a new
attendance sheet for each day...

Any help would be greatly appreciated...thank you,

~ ivalum21

Simple: you need *another table*, Attendance, related one to many to
the Enrollments table. I'd suggest using a two-field Primary Key,
ClassID and StudentID, for the enrollment table; and a three-field
Primary Key, ClassID, StudentID, and ClassDate for the Attendance
table. You'ld simply enter a record in Attendance for each day that
they attended (or perhaps have a field in the Attendance table to
explain absences, categorize them as excused or unexcused, etc.)

You don't need a new attendance *sheet* each day - you need a new
record in this table for each student, for each day. You can create a
Report filtering it to the records from a particular day, if you wish
to print out a sheet of paper - but don't assume that the "daily
sheet" needs to be a separate table or form or anything else within
Access, it's something that is created *by* the database.

John W. Vinson[MVP]

 

[Guest]

Thank you very much Mr. Vinson, that helped.

But when I try to create my relationships between Enrollments and
Attendance, I try to select the Enforce Referential Integrity and also both
Cascade options, and it tells me "No unique index found for the referenced
field of the primary table".

I have a two forms created to input the proper data instead of manually
putting it into the tables. My Attendance form works when I manually put the
information into it, but the when I use my assign student to a class form, I
want the new assignments to appear in my Attendance form...then all I'll have
to do is check my Yes/No checkbox to indicate if they were present. How do I
go about accomplishing this?

Thank you for any help you can provide,
~ivalum21

 

[John Vinson]

Thank you very much Mr. Vinson, that helped.

But when I try to create my relationships between Enrollments and
Attendance, I try to select the Enforce Referential Integrity and also both
Cascade options, and it tells me "No unique index found for the referenced
field of the primary table".

Well, that probably means that there was no Unique Index on the field
you're joining from the primary table.

What fields were you using to join the two tables?
What is the Primary Key of each table?
Is there an Index defined on either join field?

I have a two forms created to input the proper data instead of manually
putting it into the tables. My Attendance form works when I manually put the
information into it, but the when I use my assign student to a class form, I
want the new assignments to appear in my Attendance form...then all I'll have
to do is check my Yes/No checkbox to indicate if they were present. How do I
go about accomplishing this?

DON'T fall into the common trap of thinking that Forms contain data.
They don't. A Form is *just a window*.

Please repost your (current) table structures; indicate the function
of each table, its primary key, and how the tables are related. I
don't understand what *attendance* has to do with *assignments*, but
maybe I just don't understand how you're using the terms.

John W. Vinson[MVP]

 

[Guest]

Here is my table structure:

Table Name: Classes
Table Fields: ClassID (PK), ClassName

Table Name: Students
Table Fields: StudentID (PK), FirstName, LastName, etc.

Table Name: Enrollment
Table Fields: ClassID (PK), StudentID (PK), Day, Time

Table Name: Attendance
Table Fields: ClassID (PK), StudentID (PK), ClassDate (PK), Present (Yes/No)

*All tables are related by the obvious primary keys (i.e. Students.StudentID
with Enrollments.StudentID, etc.) Except with the case of Enrollment and
Attendance, I explain that below...

Then I have a form called Assign, which assigns a student to a class. The
Assign form is based on the Students table, and it has a subform based on the
Enrollments table.

Then I have a form called Attendance, which should display all students
signed up for each class on any given date. The form I have right now is
based on the Classes table, and then has a sub form based on the Students and
Attendance tables.

But when I try to create relationships between the Attendance and Enrollment
tables, it gives me an error message saying "No unique index found for the
referenced field of the primary table". Right now Attendance isn't related
to any other tables, but when I manually input the proper data into the
Attendance table, the Attendance form displays the correct information.

Thank you very much for your help,
~ivalum21

 

[John Vinson]

Then I have a form called Attendance, which should display all students
signed up for each class on any given date. The form I have right now is
based on the Classes table, and then has a sub form based on the Students and
Attendance tables.

But when I try to create relationships between the Attendance and Enrollment
tables, it gives me an error message saying "No unique index found for the
referenced field of the primary table". Right now Attendance isn't related
to any other tables, but when I manually input the proper data into the
Attendance table, the Attendance form displays the correct information.

I suspect the problem is how you're defining the join. You need to
drag BOTH ClassID and StudentID from Enrollment to the corresponding
fields in Attendance, and *then* enforce RI. You can't do it on just
one field, or on the two fields separately.

John W. Vinson[MVP]