What I did was build a crosstab query of all the students and all the classes and the dates that the classes were attended. It was then very easy to see the blank fields where dates should be. This will only work if the student is listed in the database, and all the classes are in the database.
This is the SQL statement for my query: TRANSFORM Max(tblAttendance.dtmDateofClass) AS MaxOfdtmDateofClass SELECT tblAttendance.NameOfStudent, Count(tblAttendance.dtmDateofClass) AS [Number of Classes Attended] FROM tblStudents INNER JOIN tblAttendance ON tblStudents.ID = tblAttendance.NameOfStudent WHERE (((tblAttendance.txtCourseName) Like "Word*") AND ((tblStudents.ysnCurrentEmployee)=-1)) GROUP BY tblAttendance.NameOfStudent, tblStudents.ysnCurrentEmployee PIVOT tblAttendance.txtCourseName; Dawn Crosier Application Specialist "Education Lasts a Lifetime" This message was posted to a newsgroup. Please post replies and questions to the group so that others can learn as well. -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of sgs98333 Sent: Thursday, March 02, 2006 12:59 PM To: [email protected] Subject: [ms_access] How do you search for exceptions (i.e. what isn't there) Greeting's all. I'm fairly new to Access but love the power of it. I'm cross posting this in hope of getting over a project hurdle. I'm working on a database to track training. Basically we'll input the employee's number, the date trained and the course code. The is a table with all the employees in it and a table of course codes. How do I search (query) for who didn't get trained on a particular course code? Each employee would have an entry for each course taken so the table would have several entries for each employee. The same employees may have been trained on a mix of 10 or 12 courses during a year but if I want to find out who didn't recieve training on particular course out of all the entries how do I query for it? Thanks in advance, Steve Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
