Query Problem

[SDBonner]

Hiya,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m pretty new to Access so my knowledge is still pretty limited – so be gentle!
<o> </o>
I have query that pulls out training course attendees in the following way:
<o> </o>
Person A Course A Module 1 Attended
Person A Course A Module 2 Attended
Person A Course A Module 3 Not Attended
<o> </o>
Person B Course A Module 1 Not Attended
Person B Course A Module 2 Not Attended
Person B Course A Module 3 Not Attended
<o> </o>
I can get the results for either Attended or Not Attended – for this example; if I pull the Not Attended I get the results for person A and B. I only want person B (i.e. those people who did not attend any modules at all.
<o> </o>
Am I making this horrendously complicated?!
<o> </o>
Thanks in advance for all help….

 

[Joe4]

Take a look at setting Criteria in your Query. You can place criteria on as many fields as you like.

This may ehlp: http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

 

[jackd]

Can you show us the tables and relationships of your database?

Often you would have 3 tables (more depending on your situation).

Students (studentID PK, other student specific info)

Courses(CourseID PK, other course specific info)

And a Junction Table
StudentCourse (identifying Student Took this Course)


You could use a query such as

SELECT Student.id, Student.studentName, SelectedCourses.CourseId
FROM SelectedCourses RIGHT JOIN Student ON SelectedCourses.StudentId = Student.id
WHERE (((SelectedCourses.CourseId) Is Null));

to identify Students with No Courses.