DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Trying to query a table of multiple records some same fields

  1. #1
    Kris Guest

    Trying to query a table of multiple records some same fields


    I'm trying to figure out how to do queries and to set up a table right that
    will hold students names, the date, and wheather they're present or absent
    for that date. The way I have it set up now is with multiple records of the
    same student to be able to do multiple dates that correspond with if they're
    absent or present. But I can't figure out how to do queries on the student
    based on how many absents or presents they have. I would like to be able
    to do queries to find out wich students have perfect attendance and which
    ones missed a certain number of classes.

  2. #2
    Q*bert Guest

    Re: Trying to query a table of multiple records some same fields


    I see two tables tblStudent and tblStudentDates (optionally tblDates If you
    want to show possible days for absence)

    tblStudent
    ----------
    StudentID<pk>
    FName
    LName
    etc...

    tblStudentDates <--Associative table (tblAbsence)
    ---------
    StudentID
    DateID


    tblDates
    ---------
    DateID<PK>
    Date

    From this setup:

    tblDates would have every date of the school year(ones in which a student
    could be absent from)

    tblStudent would be a list of all available students(ones that are being
    tracked)

    tblStudentDates(Absences) would consist of StudentID's and the dates for
    which they were Absent

    Query for Perfect attendance would be
    Select StudentID, FName, LName from tblStudent S LEFT JOIN tblStudentDates
    SD ON S.StudentID = SD.StudentID AND SD.StudentID is NULL

    Query for # of absent Days (greater than 5)
    Having clause could be eliminated to show count for all students
    Select StudentID, FName, LName, Count(StudentID) as [Absent Days] FROM tblStudent
    S INNER Join tblStudents SD on S.StudentID = SD.StudentID
    Group By StudentID, FName, LName
    Having Count(SD.StudentID) > 5


    The trick is to not track data that you don't need, in this case, it is more
    likely (overall) that students will be present for class, If they are not,
    then that's an exception, and therfor data that needs to be tracked, the
    lack of data in this case would mean that the student was present.

    Be happy to answer any more questions about this.

    Hope this helped,
    Q*bert


    "Kris" <MOJOkris@aol.com> wrote:
    >
    >I'm trying to figure out how to do queries and to set up a table right that
    >will hold students names, the date, and wheather they're present or absent
    >for that date. The way I have it set up now is with multiple records of

    the
    >same student to be able to do multiple dates that correspond with if they're
    >absent or present. But I can't figure out how to do queries on the student
    >based on how many absents or presents they have. I would like to be able
    >to do queries to find out wich students have perfect attendance and which
    >ones missed a certain number of classes.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center