How do I set up the IF THEN Condition in the SELECT clause?

[davidg2356]

Hi, I am trying to extract employee training data of every employee who does
not meet the minimum amount of training hours (80) within the past two years,
or employees who have no training at all within the past two years (to
include new employees who would have no training whatsoever).

I want to use an IF/THEN Condition in the SELECT statement so that if the
Completion_Date is within the 2 year time period, the code will perform one
set of functions, and if it is not within the designated time period (the
ELSE) that the other set of code is executed in that all previous hours and
potential null values are converted to zero's.

Individually, each code works; however, I just can not get the codes to work
together. I would appreciate any assistance with this.


SELECT EMPLOYEE.L_Name, EMPLOYEE.F_Name, EMPLOYEE.Middle_Initial,
EMPLOYEE.Emp_ID,

IF [NOW()-730]<=ALL(SELECT Completion_Date FROM COMPLETION)

THEN

SELECT SUM(COURSE.Course_Hours) AS Hours_Toward_Requirement,
(80-Sum(COURSE.Course_Hours)) AS Hours_Needed
FROM EMPLOYEE INNER JOIN (COURSE INNER JOIN COMPLETION ON
COURSE.Course_ID=COMPLETION.Course_ID) ON EMPLOYEE.Emp_ID=COMPLETION.Emp_ID
WHERE (((COMPLETION.Completion_Date)>=Now()-730))

ELSE

SELECT (SUM(Nz([Course_Hours],0))-SUM(Nz([Course_Hours],0))) AS
Hours_Toward_Requirement,
(80-(SUM(Nz([Course_Hours],0))-SUM(Nz([Course_Hours],0)))) AS Hours_Needed
FROM EMPLOYEE LEFT JOIN (COURSE RIGHT JOIN COMPLETION ON
COURSE.Course_ID=COMPLETION.Course_ID) ON EMPLOYEE.Emp_ID=COMPLETION.Emp_ID
WHERE (((EMPLOYEE.Emp_ID)<>All (SELECT COMPLETION.Emp_ID FROM EMPLOYEE
INNER JOIN (COURSE INNER JOIN COMPLETION ON
[COURSE].[Course_ID]=[COMPLETION].[Course_ID]) ON
[EMPLOYEE].[Emp_ID]=[COMPLETION].[Emp_ID] WHERE
Completion_Date>Now()-730)))

END IF

GROUP BY EMPLOYEE.Emp_ID, EMPLOYEE.L_Name, EMPLOYEE.F_Name,
EMPLOYEE.Middle_Initial
HAVING (((Sum(COURSE.Course_Hours))<80));

Thank you,
Sincerely,
David

 

[ChrisJ]

One answer is to use a union query

Your first query
UNION
your second query

use your IF statement in the where clause of your first query, and the
compliment of the IF statement in your second query.

 

[davidg2356]

One answer is to use a union query

Your first query
UNION
your second query

use your IF statement in the where clause of your first query, and the
compliment of the IF statement in your second query.

Thanks Chris