Am I conceptually on the right track for attendance?

[moderatelyclueless]

I'm trying to create a program to input student attendance. The interface I'm going for is an Excel-looking spreadsheet with:

• Columns as weekdays in a given month
• Rows as all students
• Cells as the attendance code (e.g., A for absent) for that day/student

I have:

• Students Table with StudentID, StudentName, DateStarted, and DateEnded.
• AttendanceCode with AttCodeID, AttCode (the letter, A), and AttText (the word, Absent)
• AttendanceTable with StudentID, AttDate, and AttCodeID
  • My Primary Key is the combined StudentID/AttDate

1. I set up relationships with the ID vars in the main tables linked to the ID vars in the AttendanceTable; is that right?
2. Do I even need an AttCodeID if all my AttCodes are unique?
3. If my overall set-up is correct, how do I make a form that looks like a simplified version of the picture I posted-- all student names as rows, all dates in July as dates, and all the cells inside as entries in AttendanceTable?

Thank you in advance.

 

[Uncle Gizmo]

I can't see a picture

 

[moderatelyclueless]

I can't see a picture

Sorry, don't have 10 posts yet to be allowed to post pictures/URLS, so I edited my post with a description of the picture I wanted to post, haha. Is it sufficiently clear?

 

[plog]

1. Yes.
2. No. Numbers make things work faster as foreign keys, but I am certain this will not be noticeable to your database.
3. You don't.


Technically its possible to achieve what you want, but in the same sense its technically possible to build 2 motorcycles out of one car: Extremely difficult for someone who is an expert. Just to be clear we are talking about a Form--an Access Object that allows you to interact with data. You want to be able to mark attendance in the layout you proprosed, right? Making a Report in Access that conforms to that layout would be easier and even achieveable because it doesn't interact with your data, it just reads your data. So when you said 'interact' I took it that you actually meant an Access Form. That would be very difficult.

Just because its the format you are use to in Excel doesn't mean its the most efficient, I think you can have one even easier to use than the layout you currently have. 95% of the Attendance for a class is going to be the same for everyone right? Everyone is probably going to be marked present or something, with the slim minority some version of Not Present (absent, tardy, early leave, excused sick, etc.). My advice is to APPEND all Students for a date with the most common AttendanceCode and then populate a form where the end user can manually change those people who weren't that most common AttendanceCode. That way its easier to achieve a workable interface (Form with sub-form) and cuts down on the amount of work the attendance submitter has to do.

The good news is, your table structure and thought process is very solid. Your foundation is good, now its just a matter of getting the right form to allow easy manipulation of those tables.