Attendance Table (1 Viewer)

[eener78]

I'm creating an attendance tracking system, the attendance system needs to : Allow an instructor to enroll students into one or more classes, and then add attendance by date to each class(es). The problem that I'm having is, attendance is tracked by the number of hours that a student attends class. For instance, if class1 is in session from 8a.m. - 12p.m. student1 may only stay 2.50 hours of that class and student2 may stay 3 hours. The teacher needs to be able to add attendance to class1 for July 12, 2013 and add hours for each student enrolled in this class session who attended on that day. I have the following so far, minus the attendance:

Students -This is just the basic demographic information that instructors will need to enter on students.
StudentID (PK)
StudentLastName
StudentFirstName
DateOfEnrollment
LevelOfStudy
DateOfBirth

Location – These are the locations in which our students can attend classes
LocationID (PK)
LocationName
County
City
LocationPhoneNumber
LocationFaxNumber

Classes – These are the classes that our students can attend.
CRNNo (PK)
ClassName
LocationID
InstructorID


Instructor – These are the instructors that teach our classes.
InstructorID (PK)
InstructorLastName
InstructorFirstName
InstructorEmailAddress

Enrollment – This table enrolls the student into a class
StudentID(PK)
CRNNo (PK)
LocationID (PK)

 

[plog]

I think Attendance needs to relate to your Enrollment table. So, I would make 2 changes to it. I would give it an autonumber primary key (EnrollmentID) and you need to drop the LocationID field. LocationID field is redundant data because you have the CRNNo in it which already relates it to a location.

The EnrollmentID field will allow you to link the new Attendance table to it. It would have these fields:

EnrollmentId
AttendanceDate
AttendanceHours


Those 3 fields would let you capture everything you needed and would link to the Enrollment table (via the EnrollmentID).

 

[Cronk]

I agree LocationID should be dropped from tblEnrolment.

The other two fields are Foreign keys into the respective tables. Add a primary key to tblEnrollment called EnrollmentID

Along the lines the OP suggested, tblAttendance should have the following fields
AttendanceID PK
EnrollmentID FK
AttendDate
AttendHours

Further, your data structure does not properly for multiple sessions of a class.

For example, if one of the classes was Intro to Excel, how would you cope with the Excel class currently running with the next two being scheduled?

To cater for this, you'd need an additional table called say tblClassSession

Fields LocationID, and InstructorID would be moved to this - these could be different for different sessions of the same class. As well add another field to distinguish the different sessions for the user - maybe start date.

 

[jdraw]

You may gain some insight from this data model at databaseanswers
http://www.databaseanswers.org/data_models/student_rosters/index.htm

Good luck.

 

[eener78]

Thank you all for your help. I made the recommended changes, but I'm still drawing a blank :banghead: :banghead: :banghead:. I may be overthinking this- I'm now trying to create the attendance form that will allow me to select a date at the top of the form and below the date will be all students enrolled in that class listed with an attendance box next to their name- this is where I will add the number of hours attended for the date. I keep thinking I need to use a query and then base the form on the query?? Please assist if possible.I've attached my relationship table. If so, I've been looking at this too long to decide which fields to pull and from where. Thanks.

 

[catalin.petrut]

In the attendance table, i suggest that you use in_time and out_time to note it (the attendance). You can set rules for in_time not to be earlier then start_time in classes table and for out_time not to be later then end_time in classes. Also, in classes table, you should have fields saying start_date and end_date for the class.

 

[Cronk]

You've done well defining your data structure and relationships before you start tackling the rest. Too many focus initially on the process first.

Ask yourself where you are now adding records. It's clearly tblAttendance. (NB I'm deliberately prefixing the table with 'tbl' to indicate that it is a table and I'd suggest you rename your tables accordingly.)

So it's not just a matter of selecting the date at the top of the form. You need select the particular EnrollmentID record.

So at the top of the form select the particular ClassID in an unbound combo box. That will then give you the source for a bound combo based on tblEnrollment.

Try that and come back if you need.

 

[eener78]

I am back. I have tried, honestly tried all day. I'm not sure If I should base the attendance form on attendance or a query. Are there any examples that I could look at possibly? Adding daily attendance to an entire class at once, is more difficult than I thought. The sad part is- it's the only part of my database that's holding me up. Examples would be great if anyone knows where I can find one. I'm usually not stumped by design- but this one is a doozie for me.

 

[plog]

Because of the complexity of relationships, I think you are going to need more than just a simple form/subform. My vision of this would require 2 subforms and some VBA.

The main form would be unbound and have 2 combo boxes to allow the user to select a class and date. On the right would be a sub form listing all students enrolled in that class--this list would refresh every time they class combo box changed. This sub form would be uneditable--students couldn't be added/edited or deleted, but there would be a button next to each student titled something like 'Add Attendance'. When clicked it would add a new record in the bottom sub-form which would be based on the Attendance table. Clicking that button would add a new record to the Attendance table with the Class in the top drop down, the date in the top drop down and the student who's button was clicked in the right sub-form. The user could then enter the hours for that student.

Atop the right subform you could even have a button saying something like 'Add Attendance For All' and it would loop through all the students and add a record for that class on that date in the Attendance table.

 

[Cronk]

Eener

As I was hinting, the data source for your data entry form is tblAttendance.

Put an unbound combo box in the form's header with row source based on tblClasses - this selects the Class and provides the CRNNo value.

In the form's detail section, have a combo box bound to EnrollmentID with rowsource showing Students in tblEnrollment for the class selected.

 

[eener78]

THANK YOU Cronk! Major brain freeze on this system; whew! Huge sigh of relief.

 

[jdraw]

Just following up after having been away for a while.
Did you ever look at the ROSTER table in the link I posted earlier?
I realize you may have solved the issue, but that data model has a lot of features and is often referenced.
Good luck.