6 Replies Latest reply on Oct 26, 2010 10:32 AM by futurex

    Counting more specifically

    futurex

      Title

      Counting more specifically

      Post

      I have a school database which tracks general student stuff like which classes they are in, attendance, etc. 

      I'm having trouble calculating the number of 'Present', 'Late' and 'Absence' occurances by student and date.

      I can count the total number of 'Present' etc. occurances for each class but can't get my head around refining it to only counting 'Present' for Student A

      For this section of the solution I'm using 3 tables

      'Students', 'Attendance_Lines', and 'PresentAbsentLate'

      'PresentAbsentLate' just contains 3 records "Present", "Absent" and "Late" so I can relate that to the 'Attendance_Lines' table.

      The 'Attendance_Lines' table relates to 'Students' and has the students name, attendance, date and class.

      I can count the total number of students in a class via the relation to the 'PresentAbsentLate' table but how do I then count the number of "Present" entries for just StudentA in the 'Attendance_Lines' table and then also the number of "Present" entries for each class over a given date range.

      I want this calculation to appear in real time on the 'Students' layout as the user scrolls through the names of Students, their attendance shows up, as over all, last 2 weeks, last month etc.  I don't want to do a find every time as it seems like that would slow everything down when the 'Attendance_Line' table gets huge.

      FutureX

        • 1. Re: Counting more specifically
          philmodjunk

          Does one record in Attendance_Lines record one day's attendance for one student or have your structured this in some other fashion?

          If you structure your tables so that you have a table where one record records a single student as Present, absent, or late for one date, the type of queries you want to run here become simple and easy to set up. You can even set up summary reports so that a single report lists each student with a subtotal count for each attendance status for any range of dates you choose to specify.

          But, I fear this may not be how you've structured your tables...

          • 2. Re: Counting more specifically
            futurex

            Yes the Attendance_Lines table is set up so one record records a single student as Present, absent or late for one date.

            • 3. Re: Counting more specifically
              philmodjunk

              Perfect!

              Then you can base a summary report layout on Attendance_lines and put fields from Students in a sub summary part when sorted by student. Make a second sub summary part below it sorted by your attendance status field (the field that reads Present, Absent or Late).

              Define a summary field as the "count of" the same attendance status field or any other field in the table that is never blank.
              Place this summary field in the second sub summary part.

              Delete the body part layout and you can perform a find for the students and dates you want, then sort your records first by student (same field you specified for the first sub summary part), then by attendance status (same field as specified for second sub summary part) and you'll get a report that looks like this:

              Jane Smith
                 Absent:     2
                 Late:         1
                 Present:   45

              John Doe
                 Absent:      1
                 //and so forth

              The only "catch" to this approach is that if a student doesn't have any attendance lines of a specific status, that entry will be missing from the report. (If Johnny has perfect attendance, you'd only see the Present subtotal, Late and Present entries would be missing.

              If that's an issue let me know and we can take a run at it from the Students table using three new relationships to separate occurrences of Attendance_Lines--it's a harder way to go and can be less flexible, but you can get a 0 entry in categories where the student did not have any attendance_lines records in that category.

              • 4. Re: Counting more specifically
                futurex

                Thanks for your help, it's really appreciated.

                Is there a way to put the Absent, Late, Present counts on a general layout for each student without having to do a summary report layout? 

                I have the main student layout as an overview for each student, it has their basic info name address phone as well as a picture, previous invoices, classes taken and I would like to add this attendance data as well.  I'd like to not have to go to another layout if possible to see the attendance counts.  Then the user can click on each section, invoices, classes, attendance and get the detailed info.

                • 5. Re: Counting more specifically
                  philmodjunk

                  It requires that  other approach based on the students table that I mentioned. (and I did remember a method that would let you put the attendance figuires in line with the students and include zero entries without all the work with the relationship graph, but that approach no longer applies given your last post. Wink)

                  Create three calculation fields in Students to serve as keys to match to specific attendance values:

                  cPresentKey  would be defined to return the value "present" (or whatever value you enter into attendance lines to mark them present.)
                  cAbsentKey would be defined to return the value "absent" and so on for cLateKey.

                  Now create thee new table occurrences of Absent_Lines (select Absent_Lines in your relationship graph and click the button with two plus signs on it) and define these three new relationships:

                  Students::StudentID = PresentAtt::StudentID AND
                  Students::cPresentKey = PresentAtt::AttStatus

                  Students::StudentID = AbsentAtt::StudentID AND
                  Students::cAbsentKey = AbsentAtt::AttStatus

                  Students::StudentID = LateAtt::StudentID AND
                  Students::cLateKey = LateAtt::AttStatus

                  Now a calculation field defined in Students as Count (PresentAtt::AttStatue) will return the count of all records for that student where they were marked present. The same approach but referring to a different table occurrence in each case will give you the other two counts.

                  If you use a pair of global date fields defined in Students, you can specify a range of attendance dates for your counts by modifying the above relationships like this:

                  Students::gDate1 < PresentAtt::Date AND
                  Students::gDate2 > PresentAtt::Date AND
                  Students::StudentID = PresentAtt::StudentID AND
                  Students::cPresentKey = PresentAtt::AttStatus

                  • 6. Re: Counting more specifically
                    futurex

                    This is great!  I knew you could do it with relationships but never thought of creating a table occurrence for each option.  

                    Thanks!