Pulling all activity that hasn't been completed, or inactivity.

[MikeyMo]

Tough one to title. But, I'm looking to query for 'outliers'.



Basically, I'm looking for all courses that have not been taken by a student. A table full of students are required to take 'x' amount of specified courses in a given curriculum. I can query which courses have been taken by whom, but I don't know how to query the students who have not taken any required course.


Three tables.

• All Courses - a list of all required courses that a student must take.
• Roster - basically a list of students, IDs, etc.
• Course completions - All course activity is recorded here. If and when a course was taken.

In this example you can clearly see that none of the students (Course Completions) have taken the "Onboarding" or "New Hire" courses (All Courses). Joe has only taken "Leadership I", and there's a mix of others. I want to be able to identify each of the students and pull the courses which they haven't taken. This would effectively produce an "outlier" query. I'm stumped.

Any suggestions? Thanks.

[June7]

ID fields appear to be text type. Why not autonumber in Roster and number in Course_Completions?

Why don't you have an autonumber field in All Courses and save it as foreign key into Course_Completions?

Should not save StudentName in Course_Completions.

Advise not to use spaces in naming convention.

Sounds like you need a Find Unmatched query but first need a dataset of all possible combinations of students/courses.

Query1:

SELECT Roster.ID, Roster.StudentName, AllCourses.Course FROM AllCourses, Roster;

Query2:
SELECT Query1.ID, Query1.StudentName, Query1.Course
FROM Course_Completions RIGHT JOIN Query1 ON (Course_Completions.ID = Query1.ID) AND (Course_Completions.Course = Query1.Course)
WHERE (((Course_Completions.ID) Is Null));

[CJ_London]

also looks like you are using table lookups in your course completions table. This will come back to bite you down the line - see this link http://access.mvps.org/access/lookupfields.htm

[ArviLaanemets]

On fly

SELECT fl.Studentname, fl.Course
FROM
     (SELECT r.ID, r.StudentName, c.Course FROM roster r, AllCourses c) fl
          LEFT JOIN CourseCompletions cc ON cc.ID = fl.ID AND cc.Course = fl.Course
WHERE cc.Course Is Null

[MikeyMo]

To answer/comment to all of the replies, Although this is a hypothetical example, the real data comes in this way. All data is imported. Rosters are manually completed in Excel and contain employee IDs. The course completions come from our learning management system and are imported from Excel.


June7

ID fields appear to be text type. Why not autonumber in Roster and number in Course_Completions?

Why don't you have an autonumber field in All Courses and save it as foreign key into Course_Completions?

Should not save StudentName in Course_Completions.

Advise not to use spaces in naming convention.

ID is a text field. The actual ID is a 7 digit alphanumeric code assigned to every employee.
The all courses table will have unique IDs for each course. I see that I didn't include that in my example. Since there will be a primary key in All Courses, I can use that and bump it up against the Course Completions table which will have the 'many' side of the one-to-many relationship.
I guess I can remove the student name field from the course completions table. It is part of the export from our learning management system. Would this break anything if it not removed?


Ajax
I'm not using lookups. All tables stand on their own.

Arvil
Haven't tried your suggestion yet. Haven't tried anyone's suggestion yet. Just wanted to clarify a few things.