Hi, Celes. Here's the "solution", which I've improved upon based on your latest business rules. However, I may not have understood those rules, so you might have two tweak the query. In any event the hard part is done. See below for my question. First, though, try out this SQL string:
Code:
SELECT tblManagers.SEID, tblManagers.LastName, tblManagers.TrainingLevel, tblCourseList.Level, tblCourseList.CourseNumber, tblCourseList.Course, [SEID] & "-" & [CourseNumber] AS MyID
FROM tblManagers, tblCourseList
WHERE (((tblCourseList.Level)<=[tblManagers].[TrainingLevel]) AND (([SEID] & "-" & [CourseNumber]) Not In (SELECT [AttendeeSEID] & "-" & [CourseNumber] AS MyID FROM TblTrainingRecord WHERE (((TblTrainingRecord.Status)="complete" Or (TblTrainingRecord.Status)="waived"));)))
ORDER BY tblManagers.SEID, tblManagers.TrainingLevel, tblCourseList.CourseNumber;
Notice that there is no join between the two tables. This is called a "Cartesian join" for reasons I'll research someday. The DLOOKUP criteria binds the two tables together in place of a join. This is the only way to get the less-than-or-equal-to "join" between the two tables.
In your background information from March 26 9pm, you mention in two places that the result should "only see people [not courses] at or below the training level". However, in your example you said that a manager hired at Level 4 "will not have to complete level 1 course". This is a contradiction. I went with the practical example's rules: In the SQL, a course is considered
eligible for a particular manager if the
COURSE'S level is equal to or less than the manager's level. For example, a Level 4 manager might have to take Level 0, 1, 2, 3, and 4 courses if they are not waived or already completed. If I am incorrect, you'll need to edit my query. It might simply be a matter of making <= to be >=.
Verify my SQL (and your changes) by (1) querying for all eligible courses and (2) querying for all completed/waived courses. All the courses listed for a manager in my SQL should be in Query 1, but not in Query 2.
Let me know if I can be of further help.
