Adding Option Group for Multiple Students in Attendance Sheet

[CherriO]

Hello All,

I am new to the board and could use some help. At work, I've been asked to create an attendance db. So below is what I created. It allows the Administrator to check off the "Present" box for each student. I have been asked to expand on the idea.

Table: Students
StudentID
StudentName
Present
Note

Table: Attendance
AttendanceID
StudentID
AttendanceDate
Present

Run an append query.

Change Requested:
For each student Replace "Present" with "1" and ".5",
1 = Full Credit .5 = Half Credit = 1/2 absence.


After 3 Absences EX:(.5,.5,.5,.5,.5,.5)=3, OR neither box selected, the students row turns red to indicate the max # of days absent has been reached.

How do I do this in Access?? I tried creating an option group but it didn't work correctly, I was able to select both options. I tried again and when I selected an option it was selected for all students. Can someone please help me through this???

Additional Detail: There are 4 new classes per year, each with a different set of students.

Thanks in Advance.

[zmbd]

CherriO:
Version of Access?
Operating System?
How experienced are you with DB Design?
How experienced are you with VBA?

What you are asking for is doable, just need to understand how much you understand (^_^)

[CherriO]

Hi Zmbd,

Thank you so very much for responding.

I am using Access 2013
Windows 7 Pro 32 bit
I am about at an intermediate level with db design. I understand building tables and relationships, forms.
VBA not very experienced.

[zmbd]

Good Morning - well at least here (^_^)
For some reason last nite I couldn't log into the site from home. It's happended a few times before and usually I just use one of the public DNS and things work again... not last nite.

In anycase, reading thru your posts, at first glance I beleave the following would work for you:

tbl_courses : Has the course title, code, etc related to classses

tbl_classes : Has the course (tbl_courses), room, instructor (tbl_people), year, quarter/semester

tbl_people : Has the people type information. Also classification as student or instructor.

tbl_enrollment : Has the Class (tbl_classes), Student (tbl_people)

tbl_attendance : enrolled (tbl_enrollment), date, attendance

Basically, by using tbl_attendance, group on the enrolled field, and count the attendance field where attendance < 1.

By way of two example tables:

tbl_classes:
[classes_pk] autonumber
[classes_fk_courses] numeric(long) 1:M tbl_courses
[classes_fk_people] numeric(long) 1:M tbl_people
[classes_room] text(10) (could also be split out into new table)
[classes_schoolyear] numeric(long)
[classes_Schoolperiod] numeric(long)

tbl_attendance:
[attendance_pk] autonumber
[attendance_fk_enrollment] numeric(long) 1:M tbl_enrollment
[attendance_date] date
[attendance_score] numeric(single) - shouldn't need anything larger than a single numeric type wih 0.5 and 1 as values; however, if for some reason you start getting errors, then change to double.

So what I would do for a given year (say this school year in the USA would be 2013/2014 the graduating class is 2014 and my child's school has fall (2013) and Spring semesters. So for the Fall-2014 attendance:

tbl_classes: query for: [classes_schoolyear]=2014 and [classes_Schoolperiod] = 1

Join with tbl_enrollment on the related field and return only those records with a match.

Join this with tbl_attendance and return only those records that match

Group this returned recordset on the [attendance_fk_enrollment] field and count only those records where the [attendance_score] <= 0.5. If the count is greater than 6 you're student has exceeded the count.
This recordset would be for EVERY student for the given year and period for every class enrolled in for that time period.

However, if you wanted only for say my DD, then you would
tbl_classes: query for: [classes_schoolyear]=2014 and [classes_Schoolperiod] = 1
Join with tbl_enrollment on the related field and return only those records with a match and further refine so that [enrolment_fk_people] = (people_pk for my DD) (now you only have the enrollment for my DD for the current year and period)
Join this with tbl_attendance and return only those records that match
Group and count as for last time.

Why not do the first way and filter down... the number of records returned in each step and if on a slow network or a large datbase, the fewer records returned the better for performance and lessor chance of altering something by mistake.

I think that you can see how querying out the class would work and so forth.

You can ofcourse add other tables to hold the point list (which is what I would do) for your attendance score etc...

Anyway, this is just one approach, I could normalize the tables a little further for example: for the classes, I could take the year and period and break those two fields out into a new table and then relate that table back to the tbl_classes, it would add another join to the query; however, the information is numeric and I don't see much payback.