Access Query that looks at results and gives a yes no

<air code>

SELECT Emp_ID, CASE course_not_completed_count WHEN 0 THEN 'MEETS REQUIREMENTS' ELSE 'DOES NOT MEET REQUIREMENTS' END as requirements
FROM (
   SELECT Emp_ID, 
     SUM(CASE WHEN ISNULL(Course_Completed) = 1 THEN 1 ELSE 0 END) as course_not_completed_count
   FROM YourTable
   GROUP BY ID) a

Select allOpen in new window

btw I have an article out there on SQL Server CASE Solutions, with a wompload of examples, if it helps.

So here is my tables that I will be using.  the dbo_emp_course.trainingcompl is the table that will store the date.  If no Date I need it to store a field that says Does Note Meet Requirements, but if it does Meets requirements.

SELECT dbo_EMP_Train.EmpID,
dbo_Emp_course.Course_name,
dbo_Emp_course.TrainingCompl

FROM
 ((dbo_EMP_Train INNER JOIN dbo_JobGroup ON dbo_EMP_Train.PrimaryPosition = dbo_JobGroup.PositionID)
 INNER JOIN dbo_TrainingCourse ON dbo_JobGroup.CourseID = dbo_TrainingCourse.CourseID)
 INNER JOIN dbo_Emp_course ON dbo_TrainingCourse.CourseName = dbo_Emp_course.Course_name;

I should add that this table could have several rows of courses some with dates and some without dates.

example:  
dbo_emp_train.empid      dbo_emp_course.course_name,      dbo_emp_course.trainingcompl
2321                                                    training a                                                            1/1/00
2321                                                     training b      
2321                                                     training c                                                     1/1/00
2400                                                      training a                                                     2/1/00
2400                                                      training b                                                      2/1/00
2400                                                     training c                                                       2/1/00

So in this example employee 2321 would be a Does not meet requirements and employee 2400 would be Meets requirements.

It is not good practice to store calculated values.  Once any one of the underlying records is updated or a new one is added, the calculated value becomes obsolete.

Did you try the query Jim posted?  It is T-SQL syntax and therefore must be run as a pass-through query in Access.  If you want something in Access syntax, then the following should do it.

SELECT dbo_EMP_Train.EmpID, IIf(Sum(IIf(IsDate(dbo_Emp_course.TrainingCompl, 1, 0)) = Count(*), "Meets Requirements", "Does Not Meet Requirements")
 FROM
  ((dbo_EMP_Train INNER JOIN dbo_JobGroup ON dbo_EMP_Train.PrimaryPosition = dbo_JobGroup.PositionID)
  INNER JOIN dbo_TrainingCourse ON dbo_JobGroup.CourseID = dbo_TrainingCourse.CourseID)
  INNER JOIN dbo_Emp_course ON dbo_TrainingCourse.CourseName = dbo_Emp_course.Course_name
Group by dbo_EMP_Train.EmpID;

You'll probably want the employee name in addition to the EmpID but you CANNOT add the course names since that would remove the aggregation.

THanks