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:
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!
There could be more than one per week. This is why I was hoping to make the day field have multiple checkboxes. If it wasn't possible to do it this way I would use a frequency field with number of sessions per week.
It would, but I do get your point. Maybe the easier way would be to have a start date for the program, number of weeks for the course, number of sessions per week, as well as number of hours per week.
The main thing I am trying to find out though is how I could use any of this data to create a timetable, so that it would automatically generate a list of all individual sessions. Do you think this is possible?
I'm gently guiding you towards identifying your entities.
Ultimately, for an attendance system you will require one row in a register table for every student that is down to attend every session, with some form of attendance mark. This guides you towards much of the information you require. As you say, the start and end dates. The specific days the session runs on and the times of each of these sessions. Also, the school\ colleges term dates and public holidays.
So there is no point trying to be too cute with your course table - you can't just have the number of sessions per week, you also need the specific days and times.
BTW - have you considered something off the shelf? This problem has been cracked many times. You also need to consider if there will be any registers taken in areas without PC access (e.g. for PE classes). Again, these problems have been solved by others and are available for purchase.
This element will just be a small part of a database that in the main part has been designed. The attendance records are simply for monitoring purposes and so PC access and so forth are not issues. In many cases the attendance details will be done after the course has finished, i.e. when the monitoring needs to be completed.
I can grasp all the theoretical sides of how to produce this but what I'm hoping for is some kind of guidance in terms of actually making this happen. Do I create a query with this data and then use a function to produce a field for each session? What function would I use for such an operation? I've tried searching the web but have found nothing that seems even similar to what I want to do.
You can't write a query or function before you have the database design bottomed out. But the short of it is yes - much of the data to populate the child tables could be generated at run time based on the course specifics (such as start and end dates).
Srsly - don't worry about the function yet. Get your database designed and then the specifics of the query will follow on from that.
I had a similar requirement, but ran into big query / form problems. My eventual approach was ugly, but quite functional -- break each course into 16 weeks (we used a 16 week term) for marks and absences (32 separate fields in all;separate mark fields for mid terms and finals also). Once you know when people are absent, you can quite easily derive number of hours absent (well, excluding holidays, special days off, etc). BTW, for the absence field, I simply used an "A", which could be entered twice (or three times) in a field for a particular week. This ugly approach made class data entry (i.e. choose a class and a week)and class level reporting surprisingly easy.
FYI - I attached a picture of the main page, and a class page. Food for thought -- I don't expect this approach to be commercially viable.
Thanks for the suggestions John. Unfortunately I think the database I am working on is a different bag of tricks. We have all kinds of different courses, some are just for a few weeks, some for a few months, and with various different hours. It's why I'm trying to make this simple and flexible. It also doesn't need to be as in depth as the screenshots that you posted.
Pootle Flump, I appreciate that it's best to get the database design sorted first but at the same time it's hard to know how to design it when you don't know what the end possibilities are!
This is where I am at the moment. 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?
All I'm trying to find out is a possible solution to my problem. You are probably right that the best method of doing this is to work on the database design until you have worked out the best possible solution. However, I have designed databases before but normally work as a web designer, and have always found that the way I work best is to try things out and work towards a solution that way. I really can't help it, I always understand things better after I've tried it out first, that's all I can say.
That is why I am just asking for a bit of practical advice. I understand the model of having three tables; a student table, a course table, and then an attendance table which will be storing the details of all the sessions and attendance. My question, as before, is if I have a table which stores the student ID with the course ID and I wanted to store the details of each session of the course would I need to have a different record for each session?