Help With an expression (1 Viewer)

[kylem4711]

Hello All, Thanks for the help

Basically I have 4 different classes that a Manager can take and to be cosidered "Complete" they have to complete 2 out of the 4. Any combination at any time. I know I would be using an If Statement, but i am not sure what direction to go in. any suggestions?

here is what I am thinking.

IIf([Job Category]='Manager' And [Course] In ('3L00643','L00640','L00641','L00642') Here is where i get stuck. i want to say something like And If the person has completed two or more out of the four classes, show the max date of the classes they have completed.

any suggestions? anything i can look up if this has been covered before?

 

[jal]

Maybe I can get you started. The sort of query you are looking for would proabbly be someting roughly in the direction of this (I asume your table is called 'CoursesTaken'. You'll have to rename it as needed.

I also create on the fly, here, a virtual table called 'AllCourses' (don't rename this one).

Basically what you're asking for is a WHERE clause that retrieves those employees who have taken more than two of the courses. I'll assume here that the empID for the employee in question is stored in a textbox called txtEmpID.

WHERE
(
SELECT COUNT(*)
FROM
(
SELECT '3L00643' as Course
UNION ALL
SELECT 'L00640' as Course
UNION ALL
SELECT 'L00641' as Course
UNION ALL
SELECT 'L00642' as Course
) as AllCourses
INNER JOIN
CoursesTaken as C ON
C.Course = AllCourses.Course
WHERE [Job Category] = 'Manager'
AND EmpID = Forms!Form1!txtEmpID
) >= 2

This query will return wrong results (the wrong Count) if an employee has taken the same course twice.