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.