Creating a class attendance system

[slaterino]

Hi,


I am trying to design a system where I can monitor attendance. The reason for this is that my client wants to know how many total hours students have attended per course. I have a good idea of the structure of my tables for doing this but am not sure how to run the queries. This is my idea for the structure and then my questions regarding it below:

tbl_courses
course_ID
course_name
start_date
end_date
day_of_week (multiple choice, monday, tuesday, etc.,)
start_time
end_time

There could be an easier way of doing this with number of sessions but it would be really useful to have the dates for each class.

Would it be possible to create a query that would automatically generate the dates for all of the classes using this information? Does anyone know what functions I would need to use in the query to do this?

Then my following question once I have managed to design the query, is how can I then record whether people attended? Each person has a unique ID and once they are enrolled on a course would have their ID next to the course ID in another table. I would then need a yes/no box to record whether they attended. I'm really unsure how I would do this part. Is it even possible? Because if not I may not pursue it.

Thanks for any help with this. Sorry the message is so long!
Russ

[NTC]

This is a classical db requirement. You need a Classes table, a Students table, and a Transaction table (that's the generic name) that holds (cross references) the ClassID and the StudentID. Both the Classes & Students tables are somewhat passive. It is the Transaction table that holds the data of which students are in which classes (and vice versa). With this basic structure you can then query any aspect.

as to your specific question; "query that would automatically generate the dates for all of the classes " : If the start date is literally correct - and the classes are 1x/week then it is easy as a +7 DateAdd method. If your start date is not literal (is just the Monday of the first week of school) and the classes vary in their frequency (Mon & Wed, Tues & Fri, Tues & Thurs, MWF, etc ) then it is somewhere between complex and impossible.

Hope this helps a little.

[slaterino]

Hey cheers for the help NTC, this is kind of where I'm heading but at the moment my problem is just understanding the technical aspect of how I can actually do it.

I have created an append query in one of my forms so that the database user can select a student and a course, click a button, and the query will then generate a new entry in another table with both of the ID numbers.

The problem I have is understanding the next step. I was thinking of using this append query to add more data to the table, i.e. the amount of sessions but can't comprehend how I would do this. Would there need to be a new entry for every session? Say there was 10 sessions in a class, would there then need to be 10 entries for every student on the class?

[NTC]

well you are keeping attendance - so yes fundamentally you need an entry of each student in each session.

your transaction table essentially has 3 fields; ClassID, StudentID, date - and 2 of those fields are lookups.

but actually entering that data is alot of manual gruntwork...so there is other stuff to consider to make the entry more streamline - - but which does not undo or change your table structure. and the best approach depends on who, how and when....if it was each teacher completing the Access form during the course itself....that design would be totally different than if it is someone is entering all courses/students/teachers from manual records all at once at the end of the day/week and needs a mass entry technique.....

[slaterino]

Okay, I am going to have to think about how this is going to work, but essentially it will be mass entry required at the end of the course so a simple way of creating the dates would be good. It's also necessary for it to be extremely flexible which is why I was thinking of maybe just having number of sessions instead of the exact dates for each session. Hmmm.