Finding a Missing Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a report that lists the dates of classes which students have
taken, the name of the class, and other vital information regarding the
student.

I now have been requested to come up with a report that lists the dates
(months) that the students did not complete a class. The students are
required to take at least one class per month for certification--in other
words the missing classes (dates).

Is there any way to accomplish this? The field that is currently being used
to populate the first report is "Dates".

Further, if there is a way to accomplish this in a query, is it possible to
find the missing dates from a certain date in time until the current date?

Any help would be much appreciated!
 
Are the students required to take specific classes, or just any class during
a given month?

The first step is to find out which students completed any class in a given
month. An aggregate query (qry_Student_Classes) that looks something like:

SELECT StudentID, Month(Class_Date) as Class_Month
FROM yourTable
WHERE Class_Date Between #1/1/2007# and #12/30/2007#
GROUP BY StudentID, Month(Class_Date)

The second step is to identify every combination of Student and Month. I
have a table (tbl_Number) that has values between 0 and 99 that I use to do
this kind of thing. This query (qry_Student_Months) might look like:

SELECT tbl_Students.StudentID, tbl_Numbers.Number as StudentMonth
FROM tbl_Students, tbl_Numbers
WHERE tbl_Numbers.Number BETWEEN 1 and 12

The third query would look something like below, and would give you a list
of the students, and the months that they did not attend a class.

SELECT qry_Student_Months.StudentID, qry_Student_Months.StudentMonth
FROM qry_Student_Months LEFT JOIN qry_Student_Classes
on qry_Student_Months.StudentID = qry_Student_Classes.StudentID
AND qry_Student_Months.StudentMonth = qry_Student_Classes.Class_Month
WHERE qry_Student_Classes.Class_Month IS NULL.

HTH
Dale
 
Back
Top