Thanks Dawn that got me on the right track. I wish I'd found this group earlier, it's a great help. Steve
--- In [email protected], "Crosier, Dawn" <[EMAIL PROTECTED]> wrote: > > 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/
