Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Attendance Report (xp/2003)

    I have a table with Date, ID and Present. IT is linked to a table of student names and a table of classes. In the present field you can enter A,P,L. Is there an elegant way to calculate % of absences in one query,or report or I need to write a series of them?
    The basic SQL for a general query is (but this just groups by those options) :
    SELECT tblClasses.ClassName, tblSection.txtSection, tblAttendance.Present, Count(tblAttendance.IdAttendance) AS CountOfIdAttendance
    FROM (tblClasses INNER JOIN (tblSection INNER JOIN tblStudentsAndClasses ON tblSection.IDSection = tblStudentsAndClasses.SectionID) ON tblClasses.ClassID = tblSection.IDClass) INNER JOIN tblAttendance ON tblStudentsAndClasses.StudentClassID = tblAttendance.StudentClassID
    GROUP BY tblClasses.ClassName, tblSection.txtSection, tblAttendance.Present
    ORDER BY tblClasses.ClassName, tblSection.txtSection, tblAttendance.Present;


    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Attendance Report (xp/2003)

    Please explain how you want to calculate the % of absences - per class, or per student, or ...?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Attendance Report (xp/2003)

    per class per section. A / (The total # of A+ P +L)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Attendance Report (xp/2003)

    I'd change the query to a crosstab query with section and class as row headers, present as column header and the count as value field. Also add a row total (this is a duplicate of the count, but as row header instead of value)
    Next, you can create a new query based on the crosstab query and define the percentage as a calculated field A / (row total).

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Attendance Report (xp/2003)

    Asusual Thanks again

Posting Permissions

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