Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Creating a class attendance system

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Does a course only get taught once per day, once per week?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2008
    Posts
    59
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So if a course ran on Monday and Wednesday, would it run at the same time on both these days?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2008
    Posts
    59
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes - all this is possible of course.

    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2008
    Posts
    59
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Recording Marks and Absences

    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.

    Regards

    John S
    Attached Thumbnails Attached Thumbnails Xida1.png   xida2.png  

  11. #11
    Join Date
    Nov 2008
    Posts
    59
    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?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is your problem - you have created forms and queries but don't know what your database design is. You've started on the second floor bathroom and the foundations are not even dug yet.

    Database design is not about what it is possible - you just model the reality.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2008
    Posts
    59
    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?

  14. #14
    Join Date
    Aug 2010
    Location
    NYC
    Posts
    10
    Not sure if I totally understand the problem but my solution would be this:

    1) Create a DATES table with one row for every single date in the time frame, ie:

    DT
    ---------
    1/1/2010
    1/2/2010
    1/3/2010
    ...
    12/29/2020
    12/30/2020
    12/31/2020

    You can easily create this in excel using the =date+1.

    You can then remove any day where there might not be a class from this table (holidays, etc.).

    2) Join this table to your course table:
    WHERE DT BETWEEN start_date and end_date
    AND day_of_week LIKE '%' & Weekday(DT) & '%'

    You'll have to check the syntax of the WEEKDAY() function. If that doesn't work you can pull the weekday in Excel and import it into the DATES table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •