Missing attendance records (Wizard won't help)

  • Thread starter Thread starter Roger Ball
  • Start date Start date
R

Roger Ball

My db tracks attendance in a training course for 1,200+ students.
However, my 30 helpers were inconsistent in entering the data. My
training course is over now and I need to know which events had no
record enterted so I can have my helpers fill in the blanks.

Looking for a query that lists studentID, EventID and Iteration which
are not listed in tbl_Attendance.

tbl_Student
- StudentID
- L_Name
.....

tbl_Attendance
- AttendanceID
- StudentID
- EventID
- Iteration (repetition of the course scheduled, e.g 2, tbl_Events
specifies total for event, e.g 5)
- StatusID (corresponding to attended, absent, class cancelled, etc)

tbl_Events
- EventID
- Event (long name, eg "PE")
- Iterations (total number of iterations of event, could be 1 to 30)

E.G Event is "PE", there are 5 scheduled (tbl_Events.Iterations).

These are records in tbl_Attendance for student 314:

StudentID EventID Iteration StatusID
314 PE 1 1
314 PE 2 0
314 PE 4 2
314 PE 5 1

Query result should be for the missing record:

314 PE 3

Thanks for you consideration in solving this problem.

RB
 
My db tracks attendance in a training course for 1,200+ students.
However, my 30 helpers were inconsistent in entering the data. My
training course is over now and I need to know which events had no
record enterted so I can have my helpers fill in the blanks.

Looking for a query that lists studentID, EventID and Iteration which
are not listed in tbl_Attendance.

tbl_Student
- StudentID
- L_Name
....

tbl_Attendance
- AttendanceID
- StudentID
- EventID
- Iteration (repetition of the course scheduled, e.g 2, tbl_Events
specifies total for event, e.g 5)
- StatusID (corresponding to attended, absent, class cancelled, etc)

tbl_Events
- EventID
- Event (long name, eg "PE")
- Iterations (total number of iterations of event, could be 1 to 30)

E.G Event is "PE", there are 5 scheduled (tbl_Events.Iterations).

These are records in tbl_Attendance for student 314:

StudentID EventID Iteration StatusID
314 PE 1 1
314 PE 2 0
314 PE 4 2
314 PE 5 1

Query result should be for the missing record:

314 PE 3

Thanks for you consideration in solving this problem.

RB
Click to expand...

The "Unmatched Query Wizard" should work, but if not, create a new Query; add
no tables; open it in SQL view and copy and paste this into the SQL window:

SELECT tbl_Events.EventID, tbl_Events.Event, tblEvents.Iterations FROM
tbl_Events LEFT JOIN tbl_Attendance ON tbl_Events.EventID =
tbl_Attendance.EventID AND tbl_Events.Iterations = tbl_Attendance.Iteration
WHERE tbl_Attendance.EventID IS NULL;

Untested air code but unless I've made a typo it should work; it's a
"frustrated outer join" query finding all records in tbl_Events which do NOT
have a match in tbl_Attendance.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
In Access query select the tbl_Events and then tbl_Attendance. create the relationships between the two tables by selecting EventID and Iteration from both tables. Click on the relationships and select all the records from the tbl_Events to be the one the data comes from. For query items, select the two data items from tbl_events and AttendanceID from tbl_Attendance. under AttendanceID, use criteria Null. This should do the trick.

Take care;

Submitted via EggHeadCafe - Software Developer Portal of Choice
ObjectDumper LINQ To Export Collection Via .NET Reflection
http://www.eggheadcafe.com/tutorial...-to-export-collection-via-net-reflection.aspx
 
This will find any StudentID plus eventId combination that does not have the
correct number of records., However, if someone managed to enter the same
StudentID + EventID + iteration number combination twice (313 PE 1) and missed
(313 PE 4) then the count would still be accurate and therefore the StudentID
would not get flagged. The other problem is if no record got created for the
studentid plus EventID then the studentId would not get flagged..

SELECT tbl_Attendance.StudentID
, tbl_Events.EventID
, Count(*)
FROM tbl_Attendance INNER JOIN tbl_Events
ON tbl_Attendance.EventID = tbl_Events.EventId
GROUP BY tbl_Attendance.StudentID
, tbl_Events.EventID
Having Count(*) <> tbl_Events.Iterations

If you really want to get the information as specified, I would add a small
table with one field and the numbers from 1 to the maximum number of iterations.

Then I would nest some queries. How I would do that depends on whether or not
every student was expected to attend each event?

Query One (create one record for each iteration and EventID for every student)
So if an event has the value of 5 for iterations, this will create five
records in the query for each student.
SELECT tbl_Students.StudentID, tbl_Events.EventID, Counter
FROM tbl_Events, tbl_Numbers.Counter , tbl_Students
WHERE tbl_Numbers.Counter <= tblEvents.Iteration

At this point you can now join this query to the tbl_Events to find missed
attendance.

SELECT qAllPossible.StudentID
, qAllPossible.EventID
, Counter as MissedIteration
FROM qAllPossible LEFT JOIN tbl_Attendance
ON qAllPossible.StudentID = tbl_Attendance.StudentID
AND qAllPossible.EventID = tbl_Attendance.EventIt
AND qAllPossible.Counter = tbl_Attendance.Iteration
WHERE tbl_Attendance.StudentID is NULL

If a every student did not have to attend ALL events then you could use the
above query and join it on studentID and EventID to the tbl_attendance to
restrict the returned results further.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top