complex SELECT query

  • Thread starter MES via AccessMonster.com
  • Start date
M

MES via AccessMonster.com

I am creating an Access database to keep track of our employees's education
requirements. More specifically, our employees are required to take certain
continuing education courses, and there are several courses. I am able to
query to list what an employee has taken using:

SELECT employee, coursenum, date, etc.
FROM course_attendance_table

However, I am unsure of how to query the courses that an employee HASN'T
taken. This would be assuming that all employees are required to take all
courses.

FYI - I have a table 'employee' containing each employee's info, then a
'course_attendance' table containing the employee, course #, and date of
attendence. I also have a 'course' table containing course # and course name.


Can someone help me with this?

Thanks in advance.
 
J

Jeff L

Here's something to try:

You will need two queries. In the first one, use the Employee table
and the Courses table. There will be no joins in this query. Output
EmployeeId, EmployeeName, CourseId, and CourseName. When you run this
query, you will get all possible Employee/Course combinations. In your
second query, use the first one and course_attendance_table. Join
EmployeeId and CourseID. Double click the line connecting the fields
and choose the option that asks you to include all records from the
first query. Do that for both fields. Output all fields from the
first query and EmployeeId and CourseId from course_attendance_table.
Put Is Null in the criteria for the course_attendance_table fields.
When you run this query, you will see all employees and the courses
they have not taken.

Hope that helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top